DML target and conflict semantics¶
DELETEresolves its target against table aliases even when a CTE has the same name. Inserts with generated primary keys andON DUPLICATE KEY UPDATEuse 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_aliasnames the table alias being deleted. A same-named CTE does not replace that target. All targets of a multi-targetDELETEare validated before rows are removed; an actual CTE cannot be a deletion target.For an
AUTO_INCREMENTprimary key, omit the key column fromINSERTto 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 UPDATEresolves a UNIQUE-key conflict to the existing base row and locks that row. If another transaction holdsSELECT ... FOR UPDATEon 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.