This content has been marked as final. Show 8 replies
It's too late for me to test this, but here's the idea. Use MERGE.
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.
update T3tm a
set mergecode = (
from T3mc b
where b.market_id = a.market_id
and rownum = 1
where market_id in (
2 rows updated.
SQL> select *
2 from T3tm
LAST_NAME MARKET_ID MERGECODE ID
-------------------- ------------------------- ---------- ----------
Smith Austin A8976 1
Jones Austin A8976 2
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.
The following keeps re-use to a minimum; that is, no mergecode will be assigned N+1 (or more) times until every mergecode in that market_id has been assigned N times.
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 ;