sql - Updating to another Database with a lengthy IN clause

Question

I'm trying to update certain columns from one DB to another in tables whose Id columns align.

I have the query below, but I fear it will update all rows because the IN clause values aren't being matched.

How do I match all those values within the IN clause to the relevant column from Table1? Or is this correct as is?

UPDATE Table1
SET NAME = T2.Name
FROM OTHERDB.[Table2] as T2
WHERE T2.Id in
(
'12345678'
 --...
}

question from:https://stackoverflow.com/questions/65905304/updating-to-another-database-with-a-lengthy-in-clause

Answer 1

Your suspicions are correct: your query will update every row.

You need a join:

UPDATE T1
SET NAME = T2.Name
FROM Table1 T1
JOIN OTHERDB.Table2 as T2 ON T2.Id = T1.Id
WHERE T1.Id in (
    '12345678',
     ...
}

This assumes that Id columns match up between databases. If that’s not the case, the join/where clauses would need adjustment.

Using an inner join means if there’s no corresponding data in the other database, there won’t be an update to null.

The where clause now looks up on the local table’s Id for efficiency.