SQL Server Updating Table From Another Table

Question

Im a Newbie to SQL Server

Could you help me out with updating a table within the same database -the Database Name is Staff.

I have 2 tables (I am trying to update the StaffID field in tblstaff with data from StaffID field in tblStaff_old )

the old one is tblstaff_old the new one is tblstaff

I have tried this code but is keeps telling me 0 rows updated.

UPDATE dbo.tblStaff
SET StaffID = dbo.tblStaff_Old.StaffID
FROM dbo.tblStaff_Old;

Thank You :)

possible duplicate of [SQL update from one Table to another based on a ID match](http://stackoverflow.com/questions/224732/sql-update-from-one-table-to-another-based-on-a-id-match) — Tab Alleman, Mar 04 '15 at 19:15

score 0 · Answer 1 · answered Mar 04 '15 at 17:23

You need to JOIN the two tables, and specify in the ON clause how those tables are joined (i.e. the below code, substitute id for however thse tables are joined)

Also note that I used table aliases s and so for readability.

UPDATE dbo.tblStaff
SET s.StaffID = so.StaffID
FROM dbo.tblStaff_Old so
   JOIN dbo.tblStaff s ON so.id = s.id

SQL Server Updating Table From Another Table

1 Answers1