Oracle MERGE self-join with change in one column

Question

I have table TABLE1, with columns COL1,COL2,COL3,COL4,COL5

If I have COL1 values as val1, I want to insert into this table, all the matching rows where COL1 = val1, such that COL1 = val2

i.e.

If started with 5 rows where COL1 = val1, after the MERGE, I get 5 more rows where COL1 = val2 and all other column values are copied as is.

So:

1) If no COL1 = val2 rows, then INSERT happens 2) If COL1 = val2 rows are present, nothing happens.

I have been trying the below. It works in 1), but for 2) but rexecuting the statement fails to insert due to uniqueness constarint based on Indexing on col1,col2,col4. (rundate is another value)

MERGE INTO TABLE1 t1
 USING ( 
          SELECT COL1,COL2COL3,COL4,COL5
          FROM TABLE1
          WHERE COL1 = val2
          AND COL4 <= rundate
          AND ( COL5 IS NULL
                OR COL5 >= rundate)
       ) t2  
 ON (t1.COL1 = t2.COL1)
 WHEN NOT MATCHED THEN
    INSERT (t1.COL1,t1.COL2,t1.COL3,t1.COL4,t1.COL5)
    VALUES (val2,t2.COL2,t2.COL3,t2.COL4,t2.COL5)
  ;

Why do you particularly want to use merge, rather than insert/select with a not-exists filter? — Alex Poole

mathguy mathguy · Accepted Answer · 2017-06-29T21:36:35

Here is one way to do it using the MERGE statement, as you requested. Like Alex Poole, I don't understand why you must use MERGE; this is a simple INSERT. MERGE is particularly good when you must both UPDATE and INSERT, depending on conditions; when you only INSERT based on conditions, a simple INSERT statement suffices.

Anyway: (using only three columns, it works the same way with five) This works for val1 = 1 and val2 = 2 and they are hard-coded, but you can change them to bind variables if needed (or any other method of providing inputs).

create table table1 ( col1 number, col2 number, col3 number );

insert into table1
  select 1, 101, 300 from dual union all
  select 1, 103, 400 from dual union all
  select 1, 130, 533 from dual union all
  select 2, 103, 400 from dual union all
  select 3, 103, 400 from dual
;

commit;

merge into table1 t
  using ( select col2, col3
          from   table1
          where  col1 = 1
        ) q
     on ( t.col1 = 2 and t.col2 = q.col2 and t.col3 = q.col3 )
when not matched then insert (col1, col2, col3) values (2, q.col2, q.col3)
;

select * from table1;

      COL1       COL2       COL3
---------- ---------- ----------
         1        101        300
         1        103        400
         1        130        533
         2        103        400
         3        103        400
         2        130        533
         2        101        300

Oracle MERGE self-join with change in one column

1 Answers