-1

I am trying to update a table's column using data (primary key) of another table. The person who created the database added a "Detail" table where all IDs where added to each row, even though the IDs were related, so actually just one ID was needed to get all other IDs. "Detail" table looks like:

RecordID    int
DepartmentID    int
ProjectID   int
FunctionID  int
TaskID  int
OwnerID int

However, "Department" table already has a ProjectID column; table "Project" has a FunctionID column, etc. So, only OwnerID is needed in this table to get to all other IDs; the rest are redundant.

Table "Function" has a "ProjectID" column that I need to populate. When I run:

select distinct(functionid), ProjectID from Detail order by FunctionID, ProjectID;

I get:

FunctionID    ProjectID
1             5
10            9
11            1
12            1
13            8
14            10
15            8
16            8
17            1
...           ...

I need the second part of:

update functions set projectid = (select ProjectID from Detail where FunctionID=?)

I need to end up a "Function" table that has those project IDs in it.

NoBullMan
  • 2,032
  • 5
  • 40
  • 93
  • seeing the downvotes, I am guessing this was either a stupid question or a poorly worded one. I used a workaround using Excel and concatenation formula to achieve what I needed. Maybe someone can just close this question. – NoBullMan Sep 16 '18 at 07:51
  • Possible duplicate of [SQL update from one Table to another based on a ID match](https://stackoverflow.com/questions/224732/sql-update-from-one-table-to-another-based-on-a-id-match) – SeanC Sep 17 '18 at 21:12

1 Answers1

0

SQL Server supports join in update statements, so all you need is an inner join:

UPDATE f
SET projectId = d.ProjectId
FROM functions As f
JOIN Detail As d 
    ON f.FunctionId = d.FunctionId
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121