Oracle – update the same count of duplicates between two tables

I have two tables UPD_REF and UPD_TO

CREATE TABLE UPD_REF
( ref number(10)
);

CREATE TABLE UPD_TO
( ref number(10),
  name varchar2(50)
);

INSERT INTO UPD_REF (ref) VALUES (2);
INSERT INTO UPD_REF (ref) VALUES (3);

INSERT INTO UPD_TO (ref, name) VALUES (1, 'ORG');
INSERT INTO UPD_TO (ref, name) VALUES (2, 'ORG');
INSERT INTO UPD_TO (ref, name) VALUES (2, 'ORG');
INSERT INTO UPD_TO (ref, name) VALUES (2, 'ORG');
INSERT INTO UPD_TO (ref, name) VALUES (3, 'ORG');

There is no key and we can have duplicates in both tables UPD_TO or UPD_REF.

I need to update the same count of rows on UPD_TO corresponding to UPD_REF.

If I do a simple update or merge like this:

merge into UPD_TO USING UPD_REF
on (UPD_TO.ref = UPD_REF.ref)
when matched then
update set UPD_TO.name = 'SET';

I have all the matching values updated:

REF  NAME
---- ----
   1 ORG 
   2 SET 
   2 SET 
   2 SET 
   3 SET 

But I need to get that:

REF  NAME
---- ----
   1 ORG 
   2 SET 
   2 ORG 
   2 ORG 
   3 SET

Any clues ?

Answer

You can update your rows by rowid, but first you need to identify that single row in each group by some ordering criteria in ROW_NUMBER(). The code is:

merge into upd_to
using (
  select t.rowid,
    row_number() over(
      partition by t.ref
      order by 1 /*Put your ordering to raise that single row to top of order by*/ asc
    ) as rn
  from UPD_TO t
  where ref in (
    select f.ref
    from upd_ref f
  )
) u
  on (upd_to.rowid = u.rowid and u.rn = 1)
when matched then update
  set name = 'SET'

| REF | NAME |
+-----+------+
|1    | ORG  |
|2    | ORG  |
|2    | ORG  |
|2    | SET  |
|3    | SET  |

If your table has more columns, then it may be good solution. If this is the exact table you have and you need to have a count of rows with the same ID and NAME, then it is better not to insert new rows, but update and count rows (of course, altering table before with new column for counter):

merge into UPD_TO
using (select <new data> ) new_data
on (UPD_TO.ref = new_data.ref)
when matched then update
  set item_count = item_count + new_data.item_count
when not matched then insert
  values(new_data.id, new_data.name, new_data.item_count)

I cannot imagine another useful reason to have such two-column table with duplicates, that’s why I’ve asked for clarification of the purpose.

UPD: New version, that updates the same row count per REF, that UPD_REF has, with fiddle containing duplicates in UPD_REF.

merge into upd_to
using (
  with base as (
    select t.rowid,
      t.ref,
      row_number() over(partition by t.ref order by 1) as rn
    from UPD_TO t
  )
  select rowid
  from base
  where (ref, rn) in (
    select
      f.ref,
      row_number() over(partition by f.ref order by 1) as rn2
    from upd_ref f
  )
) u
  on (upd_to.rowid = u.rowid)
when matched then update
  set name = 'SET'