Update statement for multiple ids

Question

I have 3 tables, i need to update 3rd table's column by calculating data from other two tables. ``` update table3 set column3= ( select t2.column3+t1.column3 from table2 t2 with (nolock) join table1 t1 on table2.id=t1.id where table2.id= 100 ) where id= 100; ``` This query works fine, it updates the 3rd table column, however if i supply IN operators like this: ``` update table3 set column3= ( select t2.column3+t1.column3 from table2 t2 with (nolock) join table1 t1 on table2.id=t1.id where table2.id IN (100,101) ) where id IN (100,101); ``` this fails and i get this message Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated. & i know this is because subquery is returning more than 1 row, how can i handle this scenario? Any hint/thought will be helpful. How do i update for multiple ids? ie. the select query value returned by ID 100 should be updated aga…

Accepted Answer

This is because you are trying to set `column3` to a returned result, and SQL expects that to be one value only (scalar). The SQL engine gets confused when you pass it more than one return value (which one should it use?...it does not assume to iterate through the results). So, if you want to update an entire result set, then you need to create a subtable from you query and join on that. Your query should look more like this

```
UPDATE Table3
SET Column3 = subtable.value
FROM Table3 
    JOIN (
        select t2.column3+t1.column3 as value, t1.id
        from table2 t2 with (nolock) join table1 t1
        on table2.id=t1.id
        where table2.id IN (100,101)
    ) AS subtable
    ON subtable.id = Table3.id
WHERE table3.id IN (100, 101)
```

Under this assumption that table3.id matches the other id's, you also really do not need the inner `where table2.id IN ...`

Update statement for multiple ids

Solution

Problem