The "n" subquery can be anything you want as long as it returns exactly one row per market_id. The DECODE is a way of comparing mergecodes even if one of them is null: this avoids doing an update on rows that are already OK.
merge into T3tm o using (select market_id, min(mergecode) mergecode from T3mc) n on (o.market_id = n.market_id and decode(o.mergecode,n.mergecode,0,1) = 1) when matched then update set mergecode = n.mergecode;
From the two tables below I want to be able to update t3tm.mergecode with any one of the mergecodes that has the same market_id ('Austin") in t3mc. it does not matter which one is associated with a particular customer.
ti3r wrote:That may not be possible. If there are more cusomers that need mergecodes than there are mergecodes in that market_id, then some mergecodes will have to be re-used.
I might have gotten excited too soon. This puts the same mergecode in both customer records. They need to be different.
It doesn't matter if there are more customers or mergecodes in the matket_id, or if the numbers happen to be the same; this statement will work in any case.
MERGE INTO t3tm dst USING ( WITH markets AS ( SELECT mergecode , market_id , COUNT (*) OVER ( PARTITION BY market_id ) AS m_cnt , ROW_NUMBER () OVER ( PARTITION BY market_id ORDER BY mergecode ) AS m_num FROM t3mc ) , customers AS ( SELECT last_name , market_id , COUNT (*) OVER ( PARTITION BY market_id ) AS c_cnt , ROW_NUMBER () OVER ( PARTITION BY market_id ORDER BY last_name ) AS c_num FROM t3tm WHERE mergecode IS NULL ) SELECT c.last_name , m.mergecode FROM markets m JOIN customers c ON c.market_id = m.market_id AND MOD ( m.m_num , LEAST (m.m_cnt, c.c_cnt) ) = MOD ( c.c_num , LEAST (m.m_cnt, c.c_cnt) ) ) src ON (dst.last_name = src.last_name) WHEN MATCHED THEN UPDATE SET dst.mergecode = src.mergecode ;