T-SQL Update based on subquery

Question

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.

Solution 1

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

Solution 2

You can use this syntax

update table1
set whatever
from table1 join table2 on something
join (subquery goes here) temp on something

Comments

[user1493545]

Well actual cases were a bit more complicated, I just tried to make simple illustrations. Still thanks a lot, didn't consider using joins in a update. Also, could I (and how) use WITH or some similar syntax so that subquery gives me value to use and the key to match on?