Computing desk
< October 23	<< Sep \| October \| Nov >>	Current desk >

Welcome to the Wikipedia Computing Reference Desk Archives
The page you are currently viewing is a transcluded archive page. While you can leave answers for any questions shown below, please ask new questions on one of the current reference desk pages.

October 24

Question about updating rows in table in Microsoft SQL Server

I have run into a rather interesting database scenario at work. We are using Microsoft SQL Server as a database.

There are two tables, let's call them TableA and TableB. Both of them have ID and Name columns. In each table, ID is unique, so it has no duplicate rows inside the table. On the other hand, the ID values are shared across the tables, because the whole point is to link rows between the two tables.

Now each table also has a unique Name value for each ID value. The thing is, the Name values in TableB are wrong, and I want to update them to be the same as in TableA.

For a single row, this can be done with something like this: Update TableB Set Name = (Select Name From TableA Where ID = 123) Where ID = 123. But is there a way to do this for multiple rows, other than writing the same update for each row separately? JIP | Talk 15:30, 24 October 2021 (UTC)[reply]

Yes. I believe you can reference link the IDs in the subselect like: Update TableB Set Name = (Select Name From TableA Where TableA.ID = TableB.ID). This is referred to as a correlated subquery.

Another approach is to use a join like

Update B
Set Name = A.Name
From TableB B
Join TableA A On A.ID = B.ID

The only difference would be for a case where TableA does not have a record matching TableB. On a side note, it usually not a good practice to store duplicate data across tables for precisely the data synchronization issues you have encountered here. See database normalization. If you find a need to combine data together in one place for convenience and ease of access, you might want to consider using a database view. -- Tom N talk/contrib 20:57, 24 October 2021 (UTC)[reply]