SQL - Merge (Upsert)

Data System Architecture

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_id) 
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





Discover More
Data System Architecture
SQL - Data Manipulation Language (DML)

Data Manipulation Language (DML) is a category of SQL statement that modify the data of a database. The principal commands are: SELECT UPDATE, INSERT, and DELETE but you can also find: ...
Sqlite Banner
Sqlite - Upsert

This page is the upsert statement in Sqlite The Sqlite upsert syntax can be found at Upsert. This page shows you some This example shows statement based...



Share this page:
Follow us:
Task Runner