Sql-server – Update multiple Ids when SET = (Sql Query)

performanceprimary-keyquery-performancesql server

I want to update multiple records however the value being set is based on the primary key of the given record. This is not an issue with a single record as I can use:

UPDATE T1
     SET A1 = (SELECT TOP 1 X FROM T2 WHERE Y = ID1)
     WHERE A2 = ID1

The IN operator works fine for the WHERE clause as it will update the correct list but not in the sub query. In the SET it will get the top 1 from all values in the ID list and I just want it for that corresponding record/ID.

Using: Microsoft T-SQL

Thank You

Best Answer

Instead of specifying the same IN list for both Y and A2, specify it only for A2 and make the subquery correlate with T1 on T2.Y = T1.A2, like this:


UPDATE
  dbo.T1
SET
  A1 = (SELECT TOP 1 T2.X FROM dbo.T2 WHERE T2.Y = T1.A2)
WHERE
  A2 IN (ID1, ID2, ...)
;

This way the subquery will pick the topmost row from T2 that matches the key of the T1 row being updated.

Please note also that using TOP n without ORDER BY will result in an arbitrary row being selected. If there can be two or more distinct values of X per Y in T2, it might be a good idea to make the results deterministic by introducing an ORDER BY clause with specific enough set of sorting criteria.

On the other hand, if X stays the same per Y, you are probably storing redundant information in T2. Perhaps, in that case you should move the X column to T1, based on the facts that a) T2.Y appears to be a reference to T1.A2 and b) A2 is the primary key of T1.

Related Question