SQL Upsert Syntax

An upsert is an insert that knows what to do when the row is already there. Loading a daily stock count, syncing a list from another system, or rerunning an import all need it: a plain INSERT stops on the first duplicate key. Every example on this page runs in DuckDB, in order, starting from this table:

create or replace table stock (
  product_id       integer primary key,
  quantity_on_hand integer,
  counted_on       date
);

insert into stock values
  (4, 8, date '2024-06-01'),
  (7, 5, date '2024-06-01');

The key decides what a conflict is

A conflict means the new row has the same value as an existing row in a PRIMARY KEY or UNIQUE column. Without one, there is nothing to conflict on, and DuckDB refuses the statement:

create or replace table stock_log (product_id integer, quantity_on_hand integer);

insert into stock_log values (4, 8)
on conflict do nothing;

The error reads There are no UNIQUE/PRIMARY KEY constraints that refer to this table. See table constraints for adding a key.

Skip duplicates: ON CONFLICT DO NOTHING

insert into stock values (4, 20, date '2024-06-10')
on conflict do nothing;

select * from stock order by product_id;

Product 4 keeps its 8 units. The statement succeeds and inserts 0 rows, so a rerun of a load cannot fail on rows it already loaded.

Overwrite: ON CONFLICT DO UPDATE

insert into stock values
  (4, 20, date '2024-06-10'),
  (11, 3, date '2024-06-10')
on conflict (product_id) do update
set quantity_on_hand = excluded.quantity_on_hand,
    counted_on       = excluded.counted_on;

select * from stock order by product_id;

excluded is the row you tried to insert; the table's own name refers to the row already stored. Product 4 now reads 20 and product 11 is new. The SET can mix them, for example to add to a running total:

insert into stock values (4, 5, date '2024-06-12')
on conflict (product_id) do update
set quantity_on_hand = stock.quantity_on_hand + excluded.quantity_on_hand,
    counted_on       = excluded.counted_on;

select * from stock where product_id = 4;

Only if newer: DO UPDATE ... WHERE

insert into stock values (4, 1, date '2024-06-05')
on conflict (product_id) do update
set quantity_on_hand = excluded.quantity_on_hand,
    counted_on       = excluded.counted_on
where excluded.counted_on > stock.counted_on;

select * from stock where product_id = 4;

The incoming count is dated June 5, older than the stored June 12 count, so the row stays at 25. Without the WHERE, a late-arriving file would overwrite newer data.

Shorthands: INSERT OR REPLACE and INSERT OR IGNORE

insert or replace into stock values (7, 50, date '2024-06-12');
insert or ignore into stock values (7, 999, date '2024-06-12');

select * from stock where product_id = 7;

insert or ignore is on conflict do nothing, and insert or replace overwrites every column of the existing row. They are short, but they say less than the long form, and the two engines that accept them do not implement replace the same way.

MERGE: one statement, several outcomes

merge into stock as s
using (
  select 4 as product_id, 0 as quantity_on_hand
  union all
  select 12, 9
) as counts
on s.product_id = counts.product_id
when matched then
  update set quantity_on_hand = counts.quantity_on_hand,
             counted_on       = date '2024-06-20'
when not matched then
  insert values (counts.product_id, counts.quantity_on_hand, date '2024-06-20');

select * from stock order by product_id;

MERGE joins a source to the target and says what to do with matched and unmatched rows. It needs no key, because the ON condition decides what matches, and a when matched then delete branch can remove rows too. It is the standard SQL form, and the one SQL Server and Oracle use.

By database