title: SQL Update with subsubquery
body: <p>I'm having a problem with the following update query in Oracle 11g:</p>
  
  <pre><code>update TABLE_A a set COL1 = 
    (SELECT b.COL2 FROM
      (SELECT ROWNUM AS ROW_NUMBER, b.COL2 from TABLE_B b where COL3 = a.COL4) 
     WHERE ROW_NUMBER = 2
    )
  </code></pre>
  
  <p>ORA-00904: "A"."COL4": invalid ID .</p>
  
  <p>So, a.COL4 is not known in the subsubquery, but I don't have an idea how to solve this.</p>
  
  <p>/Edit. What am I trying to do?</p>
  
  <p>There are multiple records in TABLE_B for every record in TABLE_A. New requirements from the customer however: TABLE_A will get 2 new columns instead, while TABLE_B will be deleted. So a representation of the first record of the subquery will be written to the first new field and the same for the second one. First record is easy, since Mike C's solution can be used with ROW_NUMBER = 1.</p>
  
  <p>Example rows:</p>
  
  <pre><code>TABLE_A
  
  | col0 | col1 | col2 | col3 | col4 |
  ------------------------------------
  |      |      |dummy2|dummy3|   1  |
  ------------------------------------
  |      |      |dummy4|dummy5|   2  |
  ------------------------------------
  
  TABLE_B
  
  | col1 | col2 | col3 |
  ----------------------
  |  d   |name1 |   1  |
  ----------------------
  |  d   |name2 |   1  |
  ----------------------
  |  d   |name3 |   1  |
  ----------------------
  |  d   |name4 |   2  |
  ----------------------
  
  
  TABLE_A after update
  
  | col0 | col1 | col2 | col3 | col4 |
  ------------------------------------
  | name1| name2|dummy2|dummy3|   1  |
  ------------------------------------
  | name4|      |dummy4|dummy5|   2  |
  ------------------------------------
  </code></pre>