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