SQL - Merge (Upsert)

1 - 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

3 - 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 ..

4 - Example

4.1 - 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

4.2 - 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);

5 - Documentation / Reference


Data Science
Data Analysis
Statistics
Data Science
Linear Algebra Mathematics
Trigonometry

Powered by ComboStrap