DML target and conflict semantics

DELETE resolves its target against table aliases even when a CTE has the same name. Inserts with generated primary keys and ON DUPLICATE KEY UPDATE use the intended conflict and row-lock behavior.

Syntax

WITH cte_name AS (SELECT ...)
DELETE target_alias FROM table_name AS target_alias WHERE ...;

INSERT INTO table_with_auto_increment (other_column) VALUES (...);

INSERT INTO table_name VALUES (...)
ON DUPLICATE KEY UPDATE column_name = VALUES(column_name);

These statements retain their existing SQL syntax.

Arguments

  • For DELETE, target_alias names the table alias being deleted. A same-named CTE does not replace that target. All targets of a multi-target DELETE are validated before rows are removed; an actual CTE cannot be a deletion target.

  • For an AUTO_INCREMENT primary key, omit the key column from INSERT to generate it. The conflict retry fix also covers tables with many small stored objects; it does not change the generated-key syntax.

  • ON DUPLICATE KEY UPDATE resolves a UNIQUE-key conflict to the existing base row and locks that row. If another transaction holds SELECT ... FOR UPDATE on it, the update waits for the row lock. An incoming primary key that differs from the resolved row is not the lock target.

Examples

DROP DATABASE IF EXISTS docs_dml_conflicts;
CREATE DATABASE docs_dml_conflicts;
USE docs_dml_conflicts;
CREATE TABLE target_rows (id INT PRIMARY KEY);
INSERT INTO target_rows VALUES (1), (2);
WITH x AS (SELECT 99 AS id) DELETE x FROM target_rows AS x WHERE x.id = 1;
SELECT id FROM target_rows ORDER BY id;
CREATE TABLE generated_ids (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, payload INT);
INSERT INTO generated_ids (payload) VALUES (1), (2);
SELECT id, payload FROM generated_ids ORDER BY id;
CREATE TABLE upsert_target (id INT PRIMARY KEY, u INT UNIQUE, v INT);
INSERT INTO upsert_target VALUES (1, 10, 100);
SELECT id, u, v FROM upsert_target ORDER BY id;
DROP DATABASE docs_dml_conflicts;

The DELETE removes id 1 from target_rows. The generated-key insert assigns two distinct IDs. To update the existing row with id 1 through its UNIQUE key, run INSERT INTO upsert_target VALUES (2, 10, 200) ON DUPLICATE KEY UPDATE v = VALUES(v); before the final SELECT.