title: T-SQL Update based on subquery
body: <p>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:</p>
  
  <pre><code>╔══════════════════════════════╗
  ║ Key1  Key2    Value   Other  ║
  ╠══════════════════════════════╣
  ║ Key11 Key21   Val1    Other1 ║
  ║ Key12 Key22   Val2    Other2 ║
  ║ Key13 Key23   Val3    Other3 ║
  ║ Key14 Key24   Val4    Other4 ║
  ╚══════════════════════════════╝
  </code></pre>
  
  <p>And I'd like to do something like:</p>
  
  <pre><code>UPDATE Table T1 
  SET T1.Value = (SELECT T2.Other 
                  FROM Table T2 
                  WHERE T2.Key2 IN ("SOME CONSTRAINT")) 
  WHERE T1.Key1 = T2.Key2
  </code></pre>
  
  <p>I know this can't work. Outer where clause can't see the <code>T2.Key2</code>.</p>
  
  <p>Other case would be updating one table based on other table.</p>
  
  <p>Say <code>Table1</code> is </p>
  
  <pre><code>╔═════════════════╗
  ║  Key    Value   ║
  ╠═════════════════╣
  ║ Key1   Val1     ║
  ║ Key2   Val2     ║
  ║ Key3   Val3     ║
  ║ Key4   Val4     ║
  ╚═════════════════╝
  </code></pre>
  
  <p>And <code>Table2</code></p>
  
  <pre><code>╔══════════════════════╗
  ║ OtherKey OtherValue  ║
  ╠══════════════════════╣
  ║ Key1      Val1       ║
  ║ Key2      Val2       ║
  ║ Key3      Val3       ║
  ║ Key4      Val4       ║
  ╚══════════════════════╝
  </code></pre>
  
  <p>Again I want to do something like </p>
  
  <pre><code>UPDATE Table1 T1 
  SET T1.Value = (SELECT T2.Value 
                  FROM Table2 T2 
                  WHERE "SOME CONDITION") 
  WHERE T1.Key = T2.OtherKey
  </code></pre>
  
  <p>Once again outer <code>WHERE</code> can't see the key in sub query. If I do it without outer <code>WHERE</code> in both cases I get error that sub-query returns more than one value which is not allowed.</p>
  
  <p>This will actually work without the second <code>WHERE</code> if its a <code>INSERT</code> statement instead of <code>UPDATE</code> one, only way I managed to get this to work for UPDATE is with loops or cursors.</p>
  
  <p>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.</p>