INSERT, UPDATE, DELETE, and RETURNING

You run an import, the row counts look right, and three weeks later the finance report is out by an amount nobody can trace. Writes fail in one direction: they touch more rows than you meant. A missing WHERE clause, a unique key you forgot existed, a TRUNCATE inside a transaction you believed was still open. The statement is four words long and the damage is proportional to the size of the table, not to the size of the mistake.

The examples that follow ran on MySQL 9.6 and SQLite 3.51, both installed on the machine that produced them, and the output is pasted as it came back. PostgreSQL is named where its behaviour differs, because it differs in ways you have to plan for rather than discover.

A multi-row INSERT is one statement, so it fails as one

There is no reason to send one INSERT per row. A single statement with several value tuples is one round trip, one parse and one commit under autocommit, and on a busy connection the round trips are most of the cost. The second useful form takes its rows from a query:

CREATE TABLE product (
  product_id  INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  sku         VARCHAR(32) NOT NULL,
  name        VARCHAR(80) NOT NULL,
  price_cents INT NOT NULL DEFAULT 0,
  stock       INT NOT NULL DEFAULT 0,
  updated_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_product_sku (sku),
  CONSTRAINT chk_price CHECK (price_cents >= 0),
  CONSTRAINT chk_stock CHECK (stock >= 0)
) ENGINE=InnoDB;

INSERT INTO product (sku, name, price_cents, stock) VALUES
  ('KB-01', 'Mechanical Keyboard', 8900, 12),
  ('MS-02', 'Trackball Mouse',     4500,  7),
  ('HP-03', 'Closed Headphones',  12900,  3);

INSERT INTO product (sku, name, price_cents, stock)
SELECT CONCAT('ARCH-', sku), CONCAT(name, ' (archive copy)'), price_cents, 0
FROM product
WHERE stock < 10;

SELECT product_id, sku, name, price_cents, stock FROM product ORDER BY product_id;

+------------+------------+----------------------------------+-------------+-------+
| product_id | sku        | name                             | price_cents | stock |
+------------+------------+----------------------------------+-------------+-------+
|          1 | KB-01      | Mechanical Keyboard              |        8900 |    12 |
|          2 | MS-02      | Trackball Mouse                  |        4500 |     7 |
|          3 | HP-03      | Closed Headphones                |       12900 |     3 |
|          4 | ARCH-MS-02 | Trackball Mouse (archive copy)   |        4500 |     0 |
|          5 | ARCH-HP-03 | Closed Headphones (archive copy) |       12900 |     0 |
+------------+------------+----------------------------------+-------------+-------+

Two things about that output are worth holding on to. The derived rows got their own ids because they are new rows; INSERT ... SELECT does not match anything. And the four-row insert is atomic at statement level. If row three violates a constraint or a unique key, rows one and two do not survive it. That is true on both engines, and the counter still moves:

CREATE TABLE multi (
  id    INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  sku   VARCHAR(16) NOT NULL UNIQUE,
  stock INT NOT NULL,
  CONSTRAINT chk_multi_stock CHECK (stock >= 0)
) ENGINE=InnoDB;

INSERT INTO multi (sku, stock) VALUES ('A',5),('B',-1),('C',9);
SELECT COUNT(*) AS rows_after_failed_batch FROM multi;
INSERT INTO multi (sku, stock) VALUES ('D',1);
SELECT GROUP_CONCAT(id ORDER BY id) AS ids FROM multi;

ERROR 3819 (HY000) at line 9: Check constraint 'chk_multi_stock' is violated.
+-------------------------+
| rows_after_failed_batch |
+-------------------------+
|                       0 |
+-------------------------+
+------+
| ids  |
+------+
| 4    |
+------+

Zero rows, and the next insert arrived at id 4. The batch consumed 1, 2 and 3 before it discovered the problem, and a rollback does not return auto-increment values to the pool. Gaps in an id column are not corruption and you should stop treating them as a signal.

Defaults belong to the column, not to the statement. Omitting a column uses its default; writing NULL into it does not:

INSERT INTO product (sku, name) VALUES ('CB-04', 'Braided Cable');
INSERT INTO product (sku, name, price_cents) VALUES ('CB-05', 'USB Hub', NULL);

+------------+-------+-------------+-------+---------------------+
| product_id | sku   | price_cents | stock | updated_at          |
+------------+-------+-------------+-------+---------------------+
|          7 | CB-04 |           0 |     0 | 2026-10-16 12:42:50 |
+------------+-------+-------------+-------+---------------------+
ERROR 1048 (23000) at line 6: Column 'price_cents' cannot be null

Triggers follow the rows, not the statement. A multi-row insert fires BEFORE INSERT and AFTER INSERT once per row, so a trigger that writes an audit row writes two audit rows for a two-row insert. That is what makes triggers a poor place for anything with a statement-level cost, and it is also why the trigger has to be written to tolerate the same row arriving twice.

An upsert resolves against a unique key, not against your intent

The upsert is the shape you want when a batch may contain rows that already exist. MySQL spells it ON DUPLICATE KEY UPDATE; PostgreSQL and SQLite spell it ON CONFLICT ... DO UPDATE. Both check the unique indexes on the target table, find a collision, and run the update branch instead of the insert.

The problem is the phrase "a collision". If the table has two unique keys, the engine resolves against whichever one it finds first, and that is not necessarily the row you had in mind.

CREATE TABLE account (
  account_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  email      VARCHAR(80) NOT NULL,
  username   VARCHAR(40) NOT NULL,
  logins     INT NOT NULL DEFAULT 0,
  UNIQUE KEY uq_email (email),
  UNIQUE KEY uq_username (username)
) ENGINE=InnoDB;

INSERT INTO account (email, username) VALUES
  ('ana@example.com', 'ana'),
  ('bo@example.com',  'bo');

INSERT INTO account (email, username, logins) VALUES ('ana@example.com', 'ana', 1) AS new
  ON DUPLICATE KEY UPDATE logins = account.logins + 1;

INSERT INTO account (email, username, logins) VALUES ('bo@example.com', 'ana', 1) AS new
  ON DUPLICATE KEY UPDATE username = new.username, logins = account.logins + 1;

ERROR 1062 (23000) at line 18: Duplicate entry 'ana' for key 'account.uq_username'

The second statement matched on email, landed on Bo's row, and then tried to set username to ana, which collides with Ana's row. The upsert failed on a key the statement never mentioned. If the two keys had pointed at compatible values instead, the statement would have quietly updated a row chosen by index order.

The two other things an upsert does not do. It does not protect against a lost update: logins = new.logins writes the value the caller read, so two concurrent callers both writing 5 leave 5, not 10. And it does not spare your triggers. Both engines fire the insert trigger on every upsert call, including the calls that end up updating:

+----+--------------------------+
| id | msg                      |
+----+--------------------------+
|  1 | BEFORE INSERT KB-01      |
|  2 | BEFORE INSERT KB-01      |
|  3 | BEFORE UPDATE KB-01 1->2 |
+----+--------------------------+
+-------+------+
| sku   | hits |
+-------+------+
| KB-01 |    2 |
+-------+------+

The statement above ran twice against an empty table. The first call inserted and wrote trace row 1. The second call wrote trace row 2 through BEFORE INSERT and then trace row 3 through BEFORE UPDATE, even though no insert happened. If your insert trigger increments a counter, publishes an event or writes to a ledger, the upsert double-counts.

One more cost, visible only in the id column:

+-------------------+
| ids_actually_used |
+-------------------+
| 1,3               |
+-------------------+
+-----+------+
| sku | hits |
+-----+------+
| A-1 |    5 |
| A-2 |    0 |
+-----+------+

Two statements reached the table and the id column shows 1 and 3. The duplicate-key branch consumed id 2 on its way to deciding to update. MySQL documents the row-alias form as INSERT ... VALUES (...) AS new ON DUPLICATE KEY UPDATE col = new.col, and with the alias in place you have to qualify every column reference on the target table, which is why the examples above write account.logins rather than logins.

SQLite's version reads differently and keeps the same trap. excluded is the name of the proposed row, so the increment is hits = hits + excluded.hits:

INSERT INTO view_counter (sku, hits) VALUES ('KB-01', 1)
  ON CONFLICT (sku) DO UPDATE SET hits = hits + excluded.hits
RETURNING sku, hits;

Adding excluded.hits rather than assigning it is the difference between an increment and a clobber, and it is the only form that survives two concurrent workers.

UPDATE without a WHERE is the most expensive typo in the discipline

Every other mistake in this lesson announces itself with an error. This one announces itself with a success message. Here is a 3,000-row ledger, settled one account at a time:

mysql> UPDATE ledger SET settled = 1 WHERE account_id = 7;
Query OK, 15 rows affected
Rows matched: 15  Changed: 15  Warnings: 0

mysql> UPDATE ledger SET settled = 1;
Query OK, 2985 rows affected
Rows matched: 3000  Changed: 2985  Warnings: 0

Read the second line twice. Rows matched: 3000 is the number that matters, and the client prints it second. In a script that checks the return value, both statements look like successes, and the second one settled the entire ledger.

MySQL's command-line client has a guard for exactly this, and you should turn it on for any session where you are typing rather than running migrations:

mysql --safe-updates mydb

ERROR 1175 (HY000) at line 1: You are using safe update mode and you tried to update a
table without a WHERE that uses a KEY column.

Safe update mode rejects an UPDATE or DELETE whose WHERE does not constrain a key column. It is a client-side check, so it protects the operator and not the application, and a non-key predicate that still selects everything is allowed through. The server-side equivalent does not exist: there is no setting that makes a full-table update an error, because a full-table update is a legitimate operation.

What the server does give you is statement atomicity and constraint enforcement. A multi-row update that violates a CHECK halfway through leaves every row untouched, including the rows it had already processed:

CREATE TABLE constraint_demo (
  id INT NOT NULL PRIMARY KEY,
  v  INT NOT NULL,
  CONSTRAINT chk_v CHECK (v >= 0)
) ENGINE=InnoDB;
INSERT INTO constraint_demo VALUES (1, 5), (2, 1), (3, 9);

UPDATE constraint_demo SET v = v - 4;

ERROR 3819 (HY000) at line 10: Check constraint 'chk_v' is violated.

+----+---+
| id | v |
+----+---+
|  1 | 5 |
|  2 | 1 |
|  3 | 9 |
+----+---+

Row 1 would have become 1, which is legal. Row 2 would have become -3, which is not. The whole statement rolled back, so the fix is to correct the data or the predicate and run it again rather than to repair the rows one at a time. Foreign keys and unique indexes behave the same way, and they report differently enough to be worth recognising:

ERROR 1062 (23000): Duplicate entry '1' for key 'constraint_demo.PRIMARY'
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
  (`m3lab`.`stock_movement`, CONSTRAINT `fk_movement_product` FOREIGN KEY
  (`product_id`) REFERENCES `product` (`product_id`))

SQLite agrees on statement atomicity. The same three-row table and the same failing update leave all three rows at their original values, and the error comes back as CHECK constraint failed: v >= 0. The engines differ on plenty, but not on this.

DELETE leaves a trail that TRUNCATE skips

DELETE FROM t removes rows through the normal write path. It fires ON DELETE triggers, it checks foreign keys per row, it writes undo information, and it can be rolled back. TRUNCATE TABLE drops and recreates the storage, which is why it is fast and why none of those things happen.

Start with the trigger. A table with an AFTER DELETE trigger that logs each removal, emptied two different ways:

+----------+------------+
| doc_rows | audit_rows |
+----------+------------+
|        0 |          1 |
+----------+------------+

Three rows were deleted with DELETE FROM doc WHERE title = 'a', which wrote one audit row, and then the table was emptied with TRUNCATE TABLE doc, which wrote nothing. MySQL's manual puts it plainly: truncate operations do not invoke ON DELETE triggers. Any audit trail, soft-delete archive or change feed fed by a delete trigger is silently bypassed, and the row count will not tell you.

Foreign keys are the second difference, and it is sharper than it looks. TRUNCATE fails when any foreign key references the table at all, whether or not the referencing table holds rows:

TRUNCATE TABLE product;
ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint
  (`m3lab`.`stock_movement`, CONSTRAINT `fk_movement_product`)

DELETE FROM product;
Query OK, 0 rows affected

The child table was empty in both cases. DELETE succeeded because there was nothing to violate; TRUNCATE refused because the constraint exists. MySQL has no CASCADE keyword on TRUNCATE, so the choices are to drop the constraint, or to truncate the child table too, or to use DELETE. PostgreSQL does have CASCADE, and requires it or a matching truncate of the referencing tables, because it takes the same shortcut for the same reason.

The third difference is the one that has cost people weekends. In MySQL TRUNCATE is DDL, and DDL causes an implicit commit:

BEGIN;
INSERT INTO pending (note) VALUES ('should have been rolled back');
TRUNCATE TABLE doc;
ROLLBACK;

+-----------------------------+
| pending_rows_after_rollback |
+-----------------------------+
|                           1 |
+-----------------------------+

The ROLLBACK returned success. The insert is still there anyway, because the TRUNCATE committed it on the way past. The same is true of CREATE TABLE, ALTER TABLE and DROP TABLE in MySQL, and it means a transaction that contains any DDL is not a transaction in the sense you were relying on:

BEGIN;
INSERT INTO ddl_demo (v) VALUES (1);
CREATE TABLE created_inside_tx (id INT) ENGINE=InnoDB;
ROLLBACK;
SELECT (SELECT COUNT(*) FROM ddl_demo) AS ddl_demo_rows;

+---------------+
| ddl_demo_rows |
+---------------+
|             1 |
+---------------+

PostgreSQL and SQLite do not do this. Both have transactional DDL, so a CREATE TABLE inside a transaction that rolls back leaves no table behind, and a TRUNCATE in PostgreSQL is documented as safely rolled back if the surrounding transaction does not commit. MySQL's TRUNCATE also resets AUTO_INCREMENT to its start value, while DELETE does not, which is the difference between an id sequence that continues and one that starts reusing values other tables still reference.

SQLite has no TRUNCATE statement at all:

sqlite> TRUNCATE TABLE product;
Error: in prepare, near "TRUNCATE": syntax error

DELETE FROM product is the only way to empty a table there, so the trigger fires and the foreign keys are checked, and the statement is transactional like any other. If you are writing SQL that has to run on all three engines, DELETE FROM is the portable spelling and TRUNCATE is the optimisation you reach for when you have checked the constraints, the triggers and the transaction around it.

RETURNING hands back the row the engine actually wrote

Every write above needed a follow-up SELECT to find out what happened, and a follow-up SELECT is a second round trip that can also be wrong: it races a concurrent writer, and its predicate may not match the rows the write touched. RETURNING removes the round trip by making the write return rows.

SQLite added it in 3.35.0, released 2021-03-12, and it works on all three write statements. PostgreSQL has had it far longer:

INSERT INTO product (sku, name, price_cents) VALUES
  ('KB-01','Mechanical Keyboard',8900),
  ('MS-02','Trackball Mouse',4500)
RETURNING product_id, sku, price_cents;

UPDATE product SET price_cents = price_cents + 1000 WHERE sku = 'MS-02'
RETURNING sku, price_cents;

┌────────────┬───────┬─────────────┐
│ product_id │  sku  │ price_cents │
├────────────┼───────┼─────────────┤
│ 1          │ KB-01 │ 8900        │
│ 2          │ MS-02 │ 4500        │
└────────────┴───────┴─────────────┘
┌───────┬─────────────┐
│  sku  │ price_cents │
├───────┼─────────────┤
│ MS-02 │ 5500        │
└───────┴─────────────┘

PostgreSQL's documentation is explicit that the values you get back are the rows as the triggers left them, which makes RETURNING the correct way to read a column a trigger computed. PostgreSQL also allows RETURNING old.col, new.col, so an update can report both sides of the change in one statement. SQLite returns the row as written but has no old/new keywords.

MySQL has no RETURNING clause. The syntax is rejected outright:

INSERT INTO product (sku,name) VALUES ('RT-01','probe') RETURNING product_id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'RETURNING product_id' at line 1

The nearest substitute is LAST_INSERT_ID(), and it is narrower than the name suggests. For a multi-row insert it returns the id of the first row in the batch, not the last, and it tells you nothing about an UPDATE or a DELETE:

+----------------+------------+
| last_insert_id | actual_ids |
+----------------+------------+
|              2 | 2,3,4      |
+----------------+------------+

So on MySQL the honest pattern for "write and read back" is a transaction: do the write, select the affected rows by the key you used, and commit. Doing the select inside the same transaction is what makes it correct, and that is the subject of the next lesson.

INSERT INTO product (sku, name, price_cents, stock) VALUES
  ('TR-01', 'Trigger A', 100, 5),
  ('TR-02', 'Trigger B', 200, -1);

INSERT INTO product (sku, name, price_cents) VALUES ('KB-01', 'duplicate');

UPDATE product SET stock = stock + 3 WHERE sku IN ('TR-01', 'TR-02');