Wikipedia:Reference desk/Archives/Computing/2021 October 24
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
editQuestion about updating rows in table in Microsoft SQL Server
editI 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)
- 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)