SQL - Merge (Upsert)

About

A merge statement execute an expression if an expression matches and an other if not.

A sequence is always executed even if a condition does not match but a function not. That's why, it's best to wrap the sequence in a function

Syntax

Database Upsert style
MySQL INSERT .. ON DUPLICATE KEY REPLACE ..
Oracle MERGE ..
PostgreSQL INSERT .. ON CONFLICT .. DO UPDATE SET ..
SQLite INSERT OR REPLACE ..
SQL Server MERGE ..

Example

Postgres

Example where cat_desc is an unique key

INSERT INTO 
public.d_category (
  cat_id,
  cat_desc,
  load_timestamp
  ) 
 select
 nextval('serial'),
 cat_desc,
 load_timestamp
 from ...
ON conflict (cat_desc) 
DO UPDATE set
cat_desc = EXCLUDED.cat_desc

Oracle

MERGE INTO bonuses D
   USING (SELECT employee_id, salary, department_id FROM employees
   WHERE department_id = 80) S
   ON (D.employee_id = S.employee_id)
   WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
     DELETE WHERE (S.salary > 8000)
   WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
     VALUES (S.employee_id, S.salary*.01)
     WHERE (S.salary <= 8000);

Documentation / Reference


Powered by ComboStrap