This content has been marked as final. Show 6 replies
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 );
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;
Hi, Pat,1 person found this helpful
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:
I suspect MERGE will be more efficient.
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 );
Edited by: Frank Kulash on Mar 9, 2010 11:02 AM
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 );
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
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 ;-)