Upsert in SQL

2023-09-25 05:29:35

Upsert is an operation that ➊ inserts new information into the database and ➋ updates present ones. Let’s have a look at the way it works in several DBMS. The examples are interactive, so you may learn and observe.

We are going to use the toy staff desk:

┌────┬───────┬────────┬────────────┬────────┐
│ id │ title  │  metropolis  │ division │ wage │
├────┼───────┼────────┼────────────┼────────┤
│ 11 │ Diane │ London │ hr         │ 70     │
│ 12 │ Bob   │ London │ hr         │ 78     │
│ 21 │ Emma  │ London │ it         │ 84     │
│ 22 │ Grace │ Berlin │ it         │ 90     │
│ 23 │ Henry │ London │ it         │ 104    │
│ 24 │ Irene │ Berlin │ it         │ 104    │
│ 31 │ Cindy │ Berlin │ gross sales      │ 96     │
│ 32 │ Dave  │ London │ gross sales      │ 96     │
└────┴───────┴────────┴────────────┴────────┘

As an example we’re including two new staff:

(25, 'Frank', 'Berlin', 'it', 120)
(33, 'Alice', 'Berlin', 'gross sales', 100)

Good day, Frank. Good day, Alice.

And updating the opposite two:

- (11, 'Diane', 'London', 'hr', 70)
+ (11, 'Diane', 'Berlin', 'hr', 70)

- (21, 'Emma', 'London', 'it', 84)
+ (21, 'Emma', 'London', 'it', 95)

Diane is transferring to Berlin, whereas Emma’s wage is rising to 95.

You’ll be able to learn this text from begin to end, or you may skip to a piece concerning the particular database engine:

MySQL/MariaDB •
SQLite •
PostgreSQL •
SQL Server •
Oracle •
Others

INSERT

Let’s strive the straightforward means:

insert into staff
(id, title, metropolis, division, wage)
values
(11, 'Diane', 'Berlin', 'hr', 70),
(21, 'Emma', 'London', 'it', 95),
(25, 'Frank', 'Berlin', 'it', 120),
(33, 'Alice', 'Berlin', 'gross sales', 100);

The primary desk is “earlier than insert”, the second is “after insert”. We’ll use this notation for all examples.

As anticipated, it fails for Diane and Emma: the worker ID is a major key, so we will not have two staff with the identical ID. We’ll should strive one thing else.

MySQL: REPLACE

MySQL’s change into inserts information with new major/distinctive keys (in our case, worker IDs) and replaces those with present keys:

change into staff
(id, title, metropolis, division, wage)
values
(11, 'Diane', 'Berlin', 'hr', 70),
(21, 'Emma', 'London', 'it', 95),
(25, 'Frank', 'Berlin', 'it', 120),
(33, 'Alice', 'Berlin', 'gross sales', 100);

Added Frank and Alice, changed Diane and Emma.

The dangerous factor about change is that it does an precise delete adopted by insert, which has some sudden side effects like firing on delete triggers.

Additionally, you may’t reference the outdated values when changing the report. For instance, you may’t set metropolis = Berlin (prev. London) for Diane.

→ Documentation

To disregard the duplicates and solely carry out inserts, you need to use insert ignore:

insert ignore into staff
(id, title, metropolis, division, wage)
values
(11, 'Diane', 'Berlin', 'hr', 70),
(21, 'Emma', 'London', 'it', 95),
(25, 'Frank', 'Berlin', 'it', 120),
(33, 'Alice', 'Berlin', 'gross sales', 100);

Added Frank and Alice, ignored Diane and Emma.

→ Documentation

change might be not one of the simplest ways to do upserts in MySQL. There’s a higher different ↓

MySQL: INSERT…ON DUPLICATE KEY UPDATE

MySQL’s on duplicate key replace inserts information with new major/distinctive keys (in our case, worker IDs) and updates those with present keys:

insert into staff
(id, title, metropolis, division, wage)
values
  (11, 'Diane', 'Berlin', 'hr', 70),
  (21, 'Emma', 'London', 'it', 95),
  (25, 'Frank', 'Berlin', 'it', 120),
  (33, 'Alice', 'Berlin', 'gross sales', 100)
on duplicate key replace
  metropolis = values(metropolis),
  wage = values(wage);

Added Frank and Alice, up to date Diane and Emma.

When updating a report, you may reference the outdated worth by column title (metropolis) and the brand new worth with the values() perform (values(metropolis)). Now we are able to truly replace a worth, not simply change it:

insert into staff
(id, title, metropolis, division, wage)
values
  (11, 'Diane', 'Berlin', 'hr', 70)
on duplicate key replace
  metropolis = concat(values(metropolis), ' (prev. ', metropolis, ')');

Up to date the town for Diane and saved the outdated worth for reference.

Beginning with MySQL 8.0, you may alias the brand new values and never use the ugly values() perform:

insert into staff
(id, title, metropolis, division, wage)
values
  (11, 'Diane', 'Berlin', 'hr', 70),
  (21, 'Emma', 'London', 'it', 95),
  (25, 'Frank', 'Berlin', 'it', 120),
  (33, 'Alice', 'Berlin', 'gross sales', 100)
  as new
on duplicate key replace
  metropolis = new.metropolis,
  wage = new.wage;

Utilizing the new alias for brand spanking new information.

→ Documentation

SQLite: INSERT OR…

SQLite’s insert or change inserts information with new major/distinctive keys (in our case, worker IDs) and replaces those with present keys:

insert or change into staff
(id, title, metropolis, division, wage)
values
(25, 'Frank', 'Berlin', 'it', 120),
(33, 'Alice', 'Berlin', 'gross sales', 100),
(11, 'Diane', 'Berlin', 'hr', 70),
(21, 'Emma', 'London', 'it', 95);

Added Frank and Alice, changed Diane and Emma.

Like MySQL’s change, SQLite’s or change deletes present duplicate information earlier than inserting the brand new ones. It fires delete triggers in some circumstances and doesn’t permit references to outdated values.

SQLite gives quite a lot of different methods as an alternative of change:

ignore: skips the duplicate information (just like MySQL’s ignore).
rollback: aborts the present assertion and rolls again your entire transaction.
abort: aborts the present assertion and reverts any adjustments made by it, however doesn’t roll again the transaction.
fail: aborts the present assertion, however doesn’t revert any adjustments already made by it, and doesn’t roll again the transaction.

insert or abort into staff
(id, title, metropolis, division, wage)
values
(11, 'Diane', 'Berlin', 'hr', 70),
(21, 'Emma', 'London', 'it', 95),
(25, 'Frank', 'Berlin', 'it', 120),
(33, 'Alice', 'Berlin', 'gross sales', 100);

Since there are conflicts, abort reverts all adjustments. The desk stays because it was.

→ Documentation

insert or change might be not one of the simplest ways to do upserts in SQLite. There’s a higher different ↓

SQLite: INSERT…ON CONFLICT

SQLite’s on battle do replace inserts information with new major/distinctive keys (in our case, worker IDs) and updates those with present keys:

insert into staff
(id, title, metropolis, division, wage)
values
  (11, 'Diane', 'Berlin', 'hr', 70),
  (21, 'Emma', 'London', 'it', 95),
  (25, 'Frank', 'Berlin', 'it', 120),
  (33, 'Alice', 'Berlin', 'gross sales', 100)
on battle do replace set
  metropolis = excluded.metropolis,
  wage = excluded.wage;

Added Frank and Alice, up to date Diane and Emma.

When updating a report, you may reference the outdated worth by column title (metropolis) and the brand new worth with the excluded prefix (excluded.metropolis).

You’ll be able to optionally disable some updates utilizing the the place clause:

insert into staff
(id, title, metropolis, division, wage)
values
  (21, 'Emma', 'London', 'it', 95),
  (22, 'Grace', 'Berlin', 'it', 105)
on battle do replace set
  wage = excluded.wage the place excluded.wage < 100;

We solely permit salaries below 100, so Emma will get up to date however Grace doesn’t.

Apart from do replace, there’s additionally do nothing, which ignores conflicts and solely inserts new information:

insert into staff
(id, title, metropolis, division, wage)
values
  (11, 'Diane', 'Berlin', 'hr', 70),
  (21, 'Emma', 'London', 'it', 95),
  (25, 'Frank', 'Berlin', 'it', 120),
  (33, 'Alice', 'Berlin', 'gross sales', 100)
on battle do nothing;

Added Frank and Alice, ignored Diane and Emma.

on battle even permits you to specify completely different guidelines for various uniqueness constraints:

create distinctive index idx_employees_name on staff(title);

insert into staff
(id, title, metropolis, division, wage)
values
  -- enhance Emma's wage to 95
  (21, 'Emma', 'London', 'it', 95),
  -- change Grace's title to Diane
  (22, 'Diane', 'Berlin', 'it', 90)
on battle (id) do replace set
  metropolis = excluded.metropolis,
  wage = excluded.wage
on battle (title) do nothing;

We solely permit distinctive names (silly, I do know), and “Diane” is already taken. So Emma’s wage is up to date, however Grace’s title will not be.

→ Documentation

Code playgrounds

Interactive examples on this publish are powered by codapi — the platform I am constructing. Use it to embed code playgrounds into your on-line course, documentation, or weblog.

PostgreSQL: INSERT…ON CONFLICT

PostgreSQL’s on battle do replace is similar to SQLite’s (in reality, SQLite’s implementation was impressed by PostgreSQL’s). It inserts information with new major/distinctive keys (in our case, worker IDs) and updates those with present keys:

insert into staff
(id, title, metropolis, division, wage)
values
  (11, 'Diane', 'Berlin', 'hr', 70),
  (21, 'Emma', 'London', 'it', 95),
  (25, 'Frank', 'Berlin', 'it', 120),
  (33, 'Alice', 'Berlin', 'gross sales', 100)
on battle (id) do replace set
  metropolis = excluded.metropolis,
  wage = excluded.wage;

Added Frank and Alice, up to date Diane and Emma.

When updating a report, you may reference the outdated worth by column title (metropolis) and the brand new worth with the excluded prefix (excluded.metropolis).

Notice that the constraint reference after on battle (it’s known as battle goal — id in our case) is required. You may as well specify a constraint title:

alter desk staff add constraint uniq_employees_name distinctive (title);

insert into staff
(id, title, metropolis, division, wage)
values
  -- change Grace's title to Diane
  (22, 'Diane', 'Berlin', 'it', 90)
on battle on constraint uniq_employees_name do nothing;

We solely permit distinctive names, and “Diane” is already taken. Grace’s title stays unchanged.

As you may see from the instance above, PostgreSQL helps do nothing identical to SQLite. It additionally helps the the place clause:

insert into staff
(id, title, metropolis, division, wage)
values
  (21, 'Emma', 'London', 'it', 95),
  (22, 'Grace', 'Berlin', 'it', 105)
on battle (id) do replace set
  wage = excluded.wage the place excluded.wage < 100;

We solely permit salaries below 100, so Emma will get up to date however Grace doesn’t.

In contrast to SQLite, PostgreSQL doesn’t permit a number of on battle clauses:

create distinctive index idx_employees_name on staff(title);

insert into staff
(id, title, metropolis, division, wage)
values
  (21, 'Emma', 'London', 'it', 95),
  (22, 'Diane', 'Berlin', 'it', 90)
on battle (id) do replace set
  metropolis = excluded.metropolis,
  wage = excluded.wage
on battle (title) do nothing;

That is an error: just one on battle is allowed.

→ Documentation

insert...on battle is a superb approach to do upserts on older PostgreSQL variations. For contemporary ones (15+) there’s a higher different ↓

SQL Commonplace: MERGE

Now that we have seen all of the artistic methods distributors have applied upsert of their programs, let us take a look at the customary means — the merge assertion:

merge into staff
utilizing (
  values
  (11, 'Diane', 'Berlin', 'hr', 70),
  (21, 'Emma', 'London', 'it', 95),
  (25, 'Frank', 'Berlin', 'it', 120),
  (33, 'Alice', 'Berlin', 'gross sales', 100)
) as new (id, title, metropolis, division, wage)
on staff.id = new.id
when matched then replace set
  metropolis = new.metropolis,
  wage = new.wage
when not matched then insert
  values (id, title, metropolis, division, wage);

Added Frank and Alice, up to date Diane and Emma.

Here’s what occurred:

We specified the merge supply within the utilizing clause (right here we use the values because the supply, however utilizing additionally accepts tables, views and subqueries).
We gave the merge supply an alias (new) for future reference.
We outlined the match standards between the merge supply (our new values) and the merge goal (the staff desk) utilizing the on clause (staff.id = new.id).
We set the motion to take when the merge supply and merge goal match by the given standards (replace metropolis and wage with new values).
We set the motion to take when the merge supply and merge goal do not match (i.e., there are new information within the supply, so we should always insert them).

Whew. That is rather a lot to course of. However the merge assertion can do far more. For instance, you may change the inserted values:

merge into staff
utilizing (
  values
  (11, 'Diane', 'Berlin', 'hr', 70),
  (21, 'Emma', 'London', 'it', 95),
  (25, 'Frank', 'Berlin', 'it', 120),
  (33, 'Alice', 'Berlin', 'gross sales', 100)
) as new (id, title, metropolis, division, wage)
on staff.id = new.id
when matched then replace set
  metropolis = new.metropolis,
  wage = new.wage
when not matched then insert
  values (id, title || ' (new!)', metropolis, division, wage);

We nonetheless replace Diane and Emma, however we additionally change Frank and Alice’s names on insert.

And even delete the matching information:

merge into staff
utilizing (
  values
  (11, 'Diane', 'Berlin', 'hr', 70),
  (21, 'Emma', 'London', 'it', 95),
  (25, 'Frank', 'Berlin', 'it', 120),
  (33, 'Alice', 'Berlin', 'gross sales', 100)
) as new (id, title, metropolis, division, wage)
on staff.id = new.id
when matched then delete
when not matched then insert
  values (id, title, metropolis, division, wage);

Added Frank and Alice, deleted Diane and Emma.

Or ignore new information altogether by omitting the when not matched:

merge into staff
utilizing (
  values
  (11, 'Diane', 'Berlin', 'hr', 70),
  (21, 'Emma', 'London', 'it', 95),
  (25, 'Frank', 'Berlin', 'it', 120),
  (33, 'Alice', 'Berlin', 'gross sales', 100)
) as new (id, title, metropolis, division, wage)
on staff.id = new.id
when matched then replace set
  metropolis = new.metropolis,
  wage = new.wage;

Up to date Diane and Emma, ignored Frank and Alice.

You’ll be able to carry out conditional inserts, updates and deletes utilizing when...and clause:

merge into staff
utilizing (
  values
  (21, 'Emma', 'London', 'it', 95),
  (22, 'Grace', 'Berlin', 'it', 105)
) as new (id, title, metropolis, division, wage)
on staff.id = new.id
when matched and new.wage < 100 then replace set
  metropolis = new.metropolis,
  wage = new.wage;

We solely permit salaries below 100, so Emma will get up to date however Grace doesn’t.

merge is supported in PostgreSQL (15+), SQL Server (2008+) and Oracle (11+). In fact, every of them has its personal quirks. For instance, PostgreSQL provides the non-standard do nothing motion, whereas SQL Server helps completely different when not matched actions relying on the place the worth is lacking — in a supply or goal dataset.

→ Documentation:
Standard •
PostgreSQL •
SQL Server •
Oracle

Different database engines

BigQuery, H2, Snowflake, DB2 — all assist merge (every to some extent).

MariaDB helps replace and on duplicate key update, identical to MySQL.

Clickhouse doesn’t have upsert SQL statements, nevertheless it does present a ReplacingMergeTree desk engine that routinely performs a replace-style upsert on insert.

Cockroachdb helps insert...on conflict (like PostgreSQL), however with an optionally available battle goal. It additionally gives an upsert assertion (ta-da!) that works like an computerized on battle do replace.

DuckDB helps insert or replace and insert or ignore (like SQLite) in addition to insert...on conflict (additionally like SQLite), however with out a number of clauses.