Command guide

advanced upserting MySQL MariaDB SQL Server PostgreSQL SQLite

Insert Or Update A Row With UPSERT

Atomically insert a row if it does not exist or update it if it does, using the database's native conflict-resolution syntax.

5/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Record a page view, inserting on first visit or incrementing on return

The home page already exists with 5 views. The upsert detects the conflict on the primary key and increments views to 6 instead of inserting a duplicate. If home had not existed, a new row with views = 1 would have been inserted.

MySQL MariaDB

Engine-specific syntax

Source table data

Setup

CREATE TABLE page_views (page VARCHAR(100) PRIMARY KEY, views INT);

INSERT INTO
  page_views (page, views)
VALUES
  ('home', 5);

Validated query

SQL

INSERT INTO
  page_views (page, views)
VALUES
  ('home', 1) ON DUPLICATE KEY
UPDATE views = views + 1;

SELECT
  page,
  views
FROM
  page_views
ORDER BY
  page;

Expected result

page	views
home	6

SQL Server

Engine-specific syntax

Source table data

Setup

CREATE TABLE page_views (page VARCHAR(100) PRIMARY KEY, views INT);

INSERT INTO
  page_views (page, views)
VALUES
  ('home', 5);

Validated query

SQL

;

MERGE page_views AS target USING (
  VALUES
    ('home', 1)
) AS source (page, views) ON target.page = source.page WHEN MATCHED THEN
UPDATE
SET
  views = target.views + 1 WHEN NOT MATCHED THEN INSERT (page, views)
VALUES
  (source.page, source.views);

SELECT
  page,
  views
FROM
  page_views
ORDER BY
  page;

Expected result

page	views
home	6

PostgreSQL

Engine-specific syntax

Source table data

Setup

CREATE TABLE page_views (page VARCHAR(100) PRIMARY KEY, views INT);

INSERT INTO
  page_views (page, views)
VALUES
  ('home', 5);

Validated query

SQL

INSERT INTO
  page_views (page, views)
VALUES
  ('home', 1) ON CONFLICT (page) DO
UPDATE
SET
  views = page_views.views + 1;

SELECT
  page,
  views
FROM
  page_views
ORDER BY
  page;

Expected result

page	views
home	6

SQLite

Engine-specific syntax

Source table data

Setup

CREATE TABLE page_views (page VARCHAR(100) PRIMARY KEY, views INT);

INSERT INTO
  page_views (page, views)
VALUES
  ('home', 5);

Validated query

SQL

INSERT INTO
  page_views (page, views)
VALUES
  ('home', 1) ON CONFLICT (page) DO
UPDATE
SET
  views = page_views.views + 1;

SELECT
  page,
  views
FROM
  page_views
ORDER BY
  page;

Expected result

page	views
home	6

Each engine uses different upsert syntax. The final table state is identical: views incremented from 5 to 6.

Useful when

Where this command helps.

recording counters or snapshots keyed by a unique id
synchronizing incoming data when rows may already exist

Explanation

What the command is doing.

An upsert avoids the race condition of checking for a row and then inserting or updating in separate statements. Each database engine has its own syntax for this operation: MySQL and MariaDB use ON DUPLICATE KEY UPDATE, PostgreSQL uses ON CONFLICT DO UPDATE, and SQL Server uses the MERGE statement. All approaches are atomic within a single statement.

Insert Or Update A Row With UPSERT

Record a page view, inserting on first visit or incrementing on return

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.