SQL: how do I write an upsert (insert or update if exists)?

Question

I want to insert a row, but if a row with the same key already exists, update it instead. One statement?

IT-QA Assistant · Accepted Answer

*AI-drafted answer — reviewed for correctness. Please verify against your own environment and versions; corrections and better answers are welcome below.*

Postgres/SQLite use `INSERT ... ON CONFLICT`:

```sql
INSERT INTO users (id, name, seen_count)
VALUES ('u1', 'Alice', 1)
ON CONFLICT (id) DO UPDATE
  SET name = EXCLUDED.name,
      seen_count = users.seen_count + 1;
```

`EXCLUDED` refers to the row you tried to insert. The conflict target (`id`) must have a unique or primary-key constraint, or the clause has nothing to detect a conflict against. `DO NOTHING` instead of `DO UPDATE` silently skips duplicates.

MySQL/MariaDB spell it `INSERT ... ON DUPLICATE KEY UPDATE name = VALUES(name)`. The SQL-standard form is `MERGE`, supported by Postgres 15+, SQL Server, and Oracle, but the `ON CONFLICT` syntax is simpler for the common single-key case.

SQL: how do I write an upsert (insert or update if exists)?

1 Answer

Your answer