Question 1

Advanced UPSERT patterns — conditional updates, xmax detection, and soft-delete-aware indexes?

Accepted Answer

**Target Audience:** Developers who already use `INSERT ... ON CONFLICT` and now hit the follow-ups: stale overwrites, "was it created?", and soft-deleted rows.
**Difficulty:** Medium
**Engines:** PostgreSQL 16.15 (`xmax`, locking behaviour) and SQLite 3.45.0 through sql.js 1.10.2 (the runnable examples). PostgreSQL 17 `MERGE ... RETURNING` is cited, not run (the local server is 16).

> **How to read this doc:** Plain language first, then a `📌 Interview term:` callout with the precise vocabular

Question 2

How do you update a table based on another table?

Accepted Answer

**Target Audience:** Developers who can write a simple UPDATE and now need to drive it from another table, such as a staging import; beginners who want the engine differences laid out.
**Difficulty:** Medium
**Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. MySQL and SQL Server syntax is cited from their manuals and was not run here.

> **How to read this doc:** Every idea is explained in plain language fir

Question 3

What is an UPSERT?

Accepted Answer

**Target Audience:** Beginners who know INSERT and UPDATE and want to learn UPSERT from zero; candidates who need the one-minute answer and the engine differences.
**Difficulty:** Medium
**Engines:** SQLite 3.45.0 (sql.js 1.10.2, which runs the examples on this page) and PostgreSQL 16.15 (the same examples plus MERGE, and two real `psql` sessions for the race). MySQL and SQL Server facts are cited, not run.

> **How to read this doc:** Every idea is explained in plain language first, then tagged

Question 4

UPSERT with INSERT … ON CONFLICT — how do you insert or update atomically without races?

Accepted Answer

**Target Audience:** Backend developers who write "create or update" code and candidates who will be asked why the naive version is unsafe.
**Difficulty:** Medium
**Engines:** PostgreSQL 16.15 (concurrency tests, the batch example) and SQLite 3.45.0 through sql.js 1.10.2 (the runnable examples on this page). MySQL and SQL Server syntax notes are cited from their documentation, not run.

> **How to read this doc:** Every idea is explained in plain language first. Then a `📌 Interview term:` callo

SQLDECODED.

Medium

Advanced UPSERT patterns — conditional updates, xmax detection, and soft-delete-aware indexes?

How do you update a table based on another table?

What is an UPSERT?

UPSERT with INSERT … ON CONFLICT — how do you insert or update atomically without races?