T-SQL Update based on subquery
sql, sql-server, sql-update, subquery
Solution
For the first case, I don't see why the subquery is needed, wouldn't be just the same as:
UPDATE Table
SET Value = Other
WHERE Key2 IN ("SOME CONSTRAINT")
AND Key1 = Key2
And the second `UPDATE` can be done with a `JOIN`:
UPDATE T1
SET T1.Value = T2.Value
FROM Table1 T1
INNER JOIN Table2 T2
ON T1.Key = T2.OtherKey
Problem
I've come a trouble with updating with sub-query where subquery should return value based on some key in row of updated cell. Usually it would be working with table like this: ``` ╔══════════════════════════════╗ ║ Key1 Key2 Value Other ║ ╠══════════════════════════════╣ ║ Key11 Key21 Val1 Other1 ║ ║ Key12 Key22 Val2 Other2 ║ ║ Key13 Key23 Val3 Other3 ║ ║ Key14 Key24 Val4 Other4 ║ ╚══════════════════════════════╝ ``` And I'd like to do something like: ``` UPDATE Table T1 SET T1.Value = (SELECT T2.Other FROM Table T2 WHERE T2.Key2 IN ("SOME CONSTRAINT")) WHERE T1.Key1 = T2.Key2 ``` I know this can't work. Outer where clause can't see the `T2.Key2`. Other case would be updating one table based on other table. Say `Table1` is ``` ╔═════════════════╗ ║ Key Value ║ ╠═════════════════╣ ║ Key1 Val1 ║ ║ Key2 Val2 ║ ║ Key3 Val3 ║ ║ Key4 Val4 ║ ╚═════════════════╝ ``` And `Table2` ``` ╔══════════════════════╗ ║ OtherKey OtherValue ║ ╠══════════════════════╣ ║ Key1 Val1 ║ ║ Key2 Val2 ║ ║ Key3 Val3 ║ ║ Key4 Val4 ║ ╚══════════════════════╝ ``` Again I want to do something like ``` UPDATE Table1 T1 SET T1.Value = (SELECT T2.Value FROM Table2 T2 WHERE "SOME CONDITION") WHERE T1.Key = T2.OtherKey ``` Once again outer `WHERE` can't see the key in sub query. If I do it without outer `WHERE` in both cases I get error that sub-query returns more than one value which is not allowed. This will actually work without the second `WHERE` if its a `INSERT` statement instead of `UPDATE` one, only way I managed to get this to work for UPDATE is with loops or cursors. Outline of 3rd variation of situation would be if I have a result set like (Key, Value) with as much value pairs as I have rows in table to be updated and want to assign "Value" to the updated column based on "Key" from set and some key from row to be updated.