UPDATE /*+ PARALLEL (16) */ OLDER_Table ta SET (ta.GTP, ta.UPDATE_DT) = (SELECT /*+ PARALLEL (16) */ tt.GTP, SYSDATE FROM NEWER_Table tt WHERE ta.cust_id = tt.cust_id AND ta.STAMP_DATE = tt.STAMP_DATE AND ROWNUM = 1) WHERE EXISTS (SELECT 1 FROM NEWER_Table tt WHERE ta.cust_id = tt.cust_id AND ta.STAMP_DATE = tt.STAMP_DATE AND (NVL(ta.GTP, 'X') != NVL(tt.GTP, 'X')));
Since I can't test it, I can't be sure.
MERGE INTO older_table dst USING ( SELECT n.cust_id , n.stamp_date , n.gtp , ROW_NUMBER () OVER ( PARTITION BY n.cust_id , n.stamp_date ORDER BY n.gtp ) AS r_num FROM newer_table n JOIN older_table o ON n.cust_id = o.cust_id AND n.stamp_date = o.stamp_date AND NVL ( n.gtp , 'X' ) != NVL ( o.gtp , 'X' ) ) src ON ( src.cust_id = dst.cust_id AND src.stamp_date = dst.stamp_date AND src.r_num = 1 ) WHEN MATCHED THEN UPDATE SET dst.gtp = src.gtp , dst.update_date = SYSDATE ;
If the combination of cust_id and stamp_id is not unique you could get the "unable to generate stable set of rows" error in a merge.
AND ROWNUM = 1)
Kodiak_Seattle wrote:Often (as in this case) an UPDATE statement needs to query another table to get the values, and then needs almost the exact same sub-query in the WHERE clause. MERGE can often combine those. This case is sort of an exception; since MERGE can't change any of the columns used in determining matches, we had to join the two tables in the USING clause.
Question: Why is Merge faster - trying to learn ?
And when you write the query, how do you format things ? is that the way you just learned or do you some tool that does it like that, also trying to learn.I do the formatting myself. I edit in Emacs, which has some nice indenting features.