6 Replies Latest reply: Mar 10, 2010 3:40 AM by Gwydion RSS

    Nested Subselect in UPDATE statement

    Gwydion
      Hi everybody,

      I have the following problem: There are two tables FOO and BAR that both have columns X and Y and a primary key. I want to fill the values X and Y of FOO with the values X and Y from BAR where FOO.key = BAR.key . There may be multiple rows from BAR with the same key so I simply select the first one. The query would look like
      UPDATE foo f
         SET ( x, y ) = ( SELECT *
                            FROM ( SELECT b.x, b.y
                                     FROM bar b
                                    WHERE f.key = b.key )                     
                           WHERE ROWNUM = 1 );
      However, this doesn't work because the alias F is not known inside the nested subquery. If I remove the outer subquery ( the one with ROWNUM=1 ) it works fine. But I need to limit the result to a single row ( in the real application I sort the innermost query by date ). Why does Oracle forget the meaning of alias F within the nested subquery?

      Pat
        • 1. Re: Nested Subselect in UPDATE statement
          Lakmal Rajapakse
          why not just do this:
          UPDATE foo f
             SET ( x, y ) = ( 
                                SELECT b.x, b.y
                                         FROM bar b
                                        WHERE f.key = b.key                      
                               and ROWNUM = 1 );
          • 2. Re: Nested Subselect in UPDATE statement
            ajallen
            How about something like
            MERGE INTO foo D
            USING ( SELECT key,
                           MIN( x ) AS x,
                           MIN( y ) AS y
                      FROM bar
                     GROUP
                        BY key ) S
            ON ( D.key = S.key )
            WHEN MATCHED THEN UPDATE 
               SET d.x = s.x,
                     d.y = s.y;
            • 3. Re: Nested Subselect in UPDATE statement
              Frank Kulash
              Hi, Pat,

              A subquery can be correlated only to its immediate parent query, not its grandparent.

              You can get the results you want using MERGE instead of UPDATE.

              You can also get the same results with only one sub-query, something like this:
              UPDATE foo f
                 SET ( x, y ) = ( SELECT MAX (x) KEEP (DENSE_RANK LAST ORDER BY a_date)
                                         ,        MAX (y) KEEP (DENSE_RANK LAST ORDER BY a_date)
                            FROM   bar
                            WHERE  key     = f.key
                          );
              I suspect MERGE will be more efficient.

              Edited by: Frank Kulash on Mar 9, 2010 11:02 AM
              • 4. Re: Nested Subselect in UPDATE statement
                Gwydion
                To simplify things I left out the sorting by date from my example query ( I just mentioned it in the text ). How would the MERGE statement look like, if I want to do something like
                UPDATE foo f
                   SET ( x, y ) = ( SELECT *
                                      FROM ( SELECT b.x, b.y
                                               FROM bar b
                                              WHERE f.key = b.key
                                              ORDER BY b.date )                     
                                     WHERE ROWNUM = 1 );
                • 5. Re: Nested Subselect in UPDATE statement
                  Lakmal Rajapakse
                  try:
                  merge into foo f
                  using (
                  select b.key , b.x, b.y from 
                    (SELECT b.key , b.x, b.y, row_number() over (partition by b.key order by b.date) rn
                     FROM bar b) b
                  where b.rn = 1
                  ) b
                  on (f.key = b.key)
                  when matched then update set
                     f.x = b.x,
                     f.y = b.y
                  • 6. Re: Nested Subselect in UPDATE statement
                    Gwydion
                    Thanks a lot! Both versions work but ( as predicted by Frank ) the UPDATE takes about 50% longer than the MERGE ( updating about 700,000 rows ). BTW I learned some new SQL-candy ;-)