Notes on Software Design

The Four Elements of Simple Design

Oracle Merge statement - Upsert

Sometimes you need a way to insert and/or update rows in a table. Idea is if the row exists update it with the given data, if not insert the new row.

This is how it's accomplished in Oracle.

drop table foobar;
create table foobar (
  PUBSTYLE_XREF_PK number primary key, 
  PUBLICATION_ID number, 
  PUBSTYLE_ID  number
);

insert into foobar values (1, 123, 4);
insert into foobar values (2, 124, 5);
insert into foobar values (3, 125, 4);


-- this will do the update
merge into foobar a 
  using foobar b 
    ON (a.PUBSTYLE_XREF_PK = 3)
  WHEN MATCHED THEN
      UPDATE SET PPUBSTYLE_ID = 14
      WHERE a.PUBSTYLE_XREF_PK = b.PUBSTYLE_XREF_PK;

-- this will do the insert or update
merge into foobar a 
  using (select 1 from dual) b ON (a.PUBSTYLE_XREF_PK = 3)
  WHEN MATCHED THEN
      UPDATE SET PUBSTYLE_ID = 14
  WHEN NOT MATCHED THEN
      INSERT (A.PUBSTYLE_XREF_PK, A.PUBLICATION_ID, a.PUBSTYLE_ID) VALUES (3, 126, 24);