title: Why does Oracle SQL update query return "invalid identifier" on existing column?
body: <p>I have an update query for an Oracle SQL db. Upon execution the query returns <code>ORA-00904: "t1"."sv_id": invalid identifier</code></p>
  
  <p>So, why do I get an "invalid identifier" error message although the column exists?</p>
  
  <p>Here is the complete query (replaced actual table and column names by dummies in np++)</p>
  
  <pre><code>UPDATE table_1 t1 SET (type) =
      CASE
          WHEN
              ((SELECT COUNT(dateCheck.id) FROM table_2 dateCheck
                WHERE dateCheck.s_id = t1.s_id AND dateCheck.sv_id = t1.sv_id) = 0)
          THEN
              (SELECT sv.type FROM table_3 sv WHERE sv.id = t1.sv_id)
          ELSE
              (SELECT type FROM
                  (SELECT d.type as type FROM table_2 d
                   WHERE d.s_id = t1.s_id AND d.sv_id = t1.sv_id
                   ORDER BY d.creationTimestamp ASC)
                   WHERE ROWNUM = 1)
          END
  </code></pre>
  
  <p>Now I don't understand why that error occurs.</p>
  
  <p>Here is what I already know:</p>
  
  <ul>
  <li>The Queries in the <code>CASE</code> statement work when executed separately, provided they are wrapped into a query that provides table_1 t1 for sure.</li>
  <li><code>t1.s_id</code> seems to work since oracle doesn't complain about that. When i change it to a column that really doesn't exist, oracle starts complaining about that non existent column before returning something about <code>t1.sv_id</code>. So somehow the alias might work, although I'm not sure about it.</li>
  <li>I'm 100% sure that the column <code>t1.sv_id</code> exists and no typo was made. Executed a query on <code>t1</code> directly and doublechecked everything in notepad by marking all occurences.</li>
  <li><p>An (completely unrelated) update query like the following works as well (note the alias <code>t1</code> is used in the select query). Don't assume table_1/2 to be the same as in the update query above, just reused the names. This should just illustrate that I successfully used an alias in an update query before.</p>
  
  <pre><code>update table_1 t1 set (t2_id) = (select id from table_2 t2 where t1.id = t2.t1_id)
  </code></pre></li>
  </ul>
  
  <h2>UPDATE</h2>
  
  <p>Thx a lot for pointing me to the "you don't have access to alises in deeper suquery layers" issue. That got me on track again pretty fast.</p>
  
  <p>So here is the query I ended up with. This seems to work fine. Eliminates the acces to t1 in the deeper layers and selects the oldest row, so that the same result should be returned from the query I expected from the original query in the ELSE part.</p>
  
  <pre><code>        UPDATE table_1 t1 SET (type) =
          CASE
             WHEN
              ((SELECT COUNT(dateCheck.id) FROM table_2 dateCheck
                WHERE dateCheck.s_id = t1.s_id AND dateCheck.sv_id = t1.sv_id) = 0)
             THEN
              (SELECT sv.type FROM table_3 sv WHERE sv.id = t1.sv_id)
             ELSE
                  (SELECT d.type as type FROM table_2 d
                   WHERE d.s_id = t1.s_id 
                      AND d.sv_id = t1.sv_id 
                      AND d.creation = (SELECT MIN(id.creation) FROM table_2 id 
                                        WHERE d.s_id = id.s_id AND d.sv_id = id.sv_id))
          END
  </code></pre>