8. DML Language

This chapter describes the data manipulation statements (DML): INSERT, REPLACE, INSERT … ON DUPLICATE KEY UPDATE (insert or update), UPDATE, DELETE, TRUNCATE, file loading (LOAD DATA, LOAD XML), the RETURNING clause, parameterized queries and prepared statements (PREPARE / EXECUTE / DEALLOCATE PREPARE).

The examples rely on two tables used throughout sections 8.1 to 8.3; each example starts from the state left by the previous ones, and the results shown are those of miraj-cli:

CREATE TABLE clients (
    id      INT AUTO_INCREMENT PRIMARY KEY,
    nom     VARCHAR(50) NOT NULL,
    ville   VARCHAR(50),
    solde   DECIMAL(10,2) DEFAULT 0
);

CREATE TABLE commandes (
    id       INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    montant  DECIMAL(10,2) NOT NULL,
    date_cmd DATE
);

8.1 INSERT#

Synopsis#

INSERT [IGNORE] INTO table [(column, ...)]
    VALUES (value, ...) [, (value, ...) ...]
    | SET column = value [, column = value ...]
    | select_query
    [ON DUPLICATE KEY UPDATE column = value [, column = value ...]]
    [RETURNING item [, item ...]]                     -- 6.9

REPLACE INTO table [(column, ...)]
    VALUES (value, ...) [, (value, ...) ...]
    | SET column = value [, column = value ...]
    | select_query
    [RETURNING item [, item ...]]

The full syntax (optional keywords, row alias) is given in §8.6.

Inserting One Row or Several Rows#

INSERT INTO clients (nom, ville) VALUES ('Alice', 'Oran');
-- OK, 1 row(s) affected

INSERT INTO clients (nom, ville, solde) VALUES
    ('Bob', 'Alger', 50.00),
    ('Chloé', 'Oran', 0),
    ('David', 'Constantine', NULL);
-- OK, 3 row(s) affected

The SET form assigns columns one by one, with no separate column list or value list:

INSERT INTO clients SET nom = 'Émile', solde = 10;

A value can be DEFAULT, which explicitly means the column's default value:

INSERT INTO clients (nom, solde) VALUES ('Fatima', DEFAULT);
SELECT * FROM clients;

+----+--------+-------------+-------+
| id | nom    | ville       | solde |
+----+--------+-------------+-------+
|  1 | Alice  | Oran        |  0.00 |
|  2 | Bob    | Alger       | 50.00 |
|  3 | Chloé  | Oran        |  0.00 |
|  4 | David  | Constantine |  NULL |
|  5 | Émile  | NULL        | 10.00 |
|  6 | Fatima | NULL        |  0.00 |
+----+--------+-------------+-------+

Alice and Fatima received the default value of solde (0.00); David has an explicit NULL. A row can be empty (VALUES ()) when all columns have a default value or are nullable. A value can be any expression, subquery included (VALUES ((SELECT MAX(id) FROM t) + 1)).

INSERT ... SELECT#

The inserted rows come from the result of a query rather than from a VALUES list:

INSERT INTO commandes (client_id, montant, date_cmd) VALUES
    (1, 80.00, '2026-10-01'), (1, 45.50, '2026-10-03'),
    (2, 20.00, '2026-10-05'), (3, 150.00, '2026-10-06');

CREATE TABLE gros_clients (client_id INT, total DECIMAL(10,2));

INSERT INTO gros_clients
SELECT client_id, SUM(montant)
FROM commandes
GROUP BY client_id
HAVING SUM(montant) > 100;
-- OK, 2 row(s) affected

SELECT * FROM gros_clients;

+-----------+--------+
| client_id | total  |
+-----------+--------+
|         1 | 125.50 |
|         3 | 150.00 |
+-----------+--------+

INSERT IGNORE turns into warnings the errors that would otherwise make the row fail (duplicate key, row without a parent, value too long, NULL in a NOT NULL column): the offending row is skipped or its value corrected, the others are inserted (details in §8.6).

AUTO_INCREMENT#

An AUTO_INCREMENT column automatically receives the next value when the insertion does not give it one (column absent from the list, explicit NULL or DEFAULT). LAST_INSERT_ID() returns, within the session, the value assigned by the last insertion; for a multi-row INSERT, the first value assigned:

INSERT INTO clients (nom) VALUES ('Grace');
SELECT LAST_INSERT_ID();                         -- 7
INSERT INTO clients (nom) VALUES ('Hugo'), ('Inès');
SELECT LAST_INSERT_ID();                         -- 8 (Hugo; Inès received 9)

Explicitly giving a value greater than the current counter advances it: after INSERT INTO t (id, nom) VALUES (10, 'x'), the next row without a value receives 11.

DEFAULT and Generated Columns#

A column omitted from the column list receives its default value (the definition's DEFAULT, or NULL if the column is nullable without a DEFAULT). A DEFAULT can be an expression, evaluated row by row and able to reference the other columns of the same row, or a sequence (DEFAULT NEXTVAL(seq), §8.7).

A generated column (GENERATED ALWAYS AS (expr), VIRTUAL or STORED/PERSISTENT) is never populated by the insertion: an explicit value given for a generated column is ignored (it is not rejected), and the column takes the value of its expression (example in §8.6).

REPLACE#

REPLACE INTO inserts a row like INSERT, but if it conflicts with an existing row on the primary key or a UNIQUE constraint, the conflicting row is first deleted before the insertion (DELETE and INSERT triggers are executed as for these statements). Columns not named take their default value:

REPLACE INTO clients (id, nom, ville) VALUES (1, 'Alice', 'Alger');
-- OK, 2 row(s) affected        (1 deleted + 1 inserted)
SELECT * FROM clients WHERE id = 1;

+----+-------+-------+-------+
| id | nom   | ville | solde |
+----+-------+-------+-------+
|  1 | Alice | Alger |  0.00 |
+----+-------+-------+-------+

REPLACE also accepts the SET and SELECT forms:

CREATE TABLE archive (id INT PRIMARY KEY, nom VARCHAR(50));
REPLACE INTO archive (id, nom) SELECT id, nom FROM clients WHERE ville = 'Alger';
-- OK, 2 row(s) affected        (Alice and Bob inserted)
REPLACE INTO archive SET id = 2, nom = 'Bob B.';
-- OK, 2 row(s) affected        (old row 2 deleted, new one inserted)

ON DUPLICATE KEY UPDATE#

An alternative to REPLACE that updates the conflicting row instead of replacing it: the existing row keeps its identity (no deletion, no DELETE trigger) and only the columns named in ON DUPLICATE KEY UPDATE are modified. A row without a conflict is inserted normally.

INSERT INTO clients (id, nom, solde) VALUES (2, 'Bob', 100)
ON DUPLICATE KEY UPDATE solde = solde + VALUES(solde);
-- OK, 2 row(s) affected        (existing row modified: solde 50.00 -> 150.00)

VALUES(column) designates, in the ON DUPLICATE KEY UPDATE clause, the value that the conflicting row would have received on insertion. The proposed row can also be given an alias (VALUES ... AS alias), the recommended form:

INSERT INTO clients (id, nom, solde) VALUES (2, 'Bob', 100) AS nouvelle
ON DUPLICATE KEY UPDATE solde = solde + nouvelle.solde;
SELECT id, nom, solde FROM clients WHERE id = 2;

+----+-----+--------+
| id | nom | solde  |
+----+-----+--------+
|  2 | Bob | 250.00 |
+----+-----+--------+

Typical Errors#

8.2 UPDATE#

Synopsis#

UPDATE [LOW_PRIORITY] [IGNORE] table [[AS] alias]
    SET column = value | DEFAULT [, column = value ...]
    [WHERE condition]
    [ORDER BY ...] [LIMIT n]

UPDATE [LOW_PRIORITY] [IGNORE] table1 [[AS] alias1]
    {[INNER] JOIN | CROSS JOIN | LEFT [OUTER] JOIN | RIGHT [OUTER] JOIN | ,} table2 [[AS] alias2]
        [ON condition | USING (column, ...)]
    [...other joins...]
    SET column = value [, column = value ...]
    [WHERE condition]

The assigned column can be qualified by its table or by its database and table (SET c.solde = …, SET ventes.clients.solde = …).

Single-Table UPDATE#

UPDATE clients SET solde = solde + 20 WHERE ville = 'Oran';
-- OK, 1 row(s) affected        (Chloé: 0.00 -> 20.00)

ORDER BY and LIMIT, on a single-table UPDATE, choose which of the rows selected by WHERE are actually modified:

UPDATE clients SET solde = -5 WHERE id IN (5, 6);
UPDATE clients SET solde = 0 WHERE solde < 0 ORDER BY id LIMIT 1;
-- OK, 1 row(s) affected
SELECT id, nom, solde FROM clients WHERE id IN (5, 6);

+----+--------+-------+
| id | nom    | solde |
+----+--------+-------+
|  5 | Émile  |  0.00 |
|  6 | Fatima | -5.00 |
+----+--------+-------+

Multi-Table UPDATE (Update with a Join)#

UPDATE accepts the same join types as a SELECT (see chapter 9): JOIN / INNER JOIN, CROSS JOIN, the comma, LEFT [OUTER] JOIN, RIGHT [OUTER] JOIN, with ON or USING, aliases, and a derived table (subquery) as the joined table. Only one table is actually modified by the statement: the first table of the join (the one following UPDATE). The other tables of the join serve only to compute the new values and to filter rows.

UPDATE clients c
JOIN commandes o ON o.client_id = c.id
SET c.solde = c.solde - o.montant
WHERE o.id = 3;
-- OK, 1 row(s) affected        (Bob: 250.00 -> 230.00)

With an outer join, to touch the rows without a match (here, customers without orders):

UPDATE clients c
LEFT JOIN commandes o ON o.client_id = c.id
SET c.solde = c.solde + 1
WHERE o.id IS NULL;
-- OK, 6 row(s) affected

With a derived table as the source of the values:

UPDATE clients c
JOIN (
    SELECT client_id, SUM(montant) AS total
    FROM commandes
    GROUP BY client_id
) t ON t.client_id = c.id
SET c.solde = c.solde - t.total;
-- OK, 3 row(s) affected

SELECT * FROM clients ORDER BY id;

+----+--------+-------------+---------+
| id | nom    | ville       | solde   |
+----+--------+-------------+---------+
|  1 | Alice  | Alger       | -125.50 |
|  2 | Bob    | Alger       |  210.00 |
|  3 | Chloé  | Oran        | -130.00 |
|  4 | David  | Constantine |    NULL |
|  5 | Émile  | NULL        |    1.00 |
|  6 | Fatima | NULL        |   -4.00 |
|  7 | Grace  | NULL        |    1.00 |
|  8 | Hugo   | NULL        |    1.00 |
|  9 | Inès   | NULL        |    1.00 |
+----+--------+-------------+---------+

Rules specific to multi-table UPDATE:

• SET can only assign columns of the first table (the one following UPDATE); assigning a column of another table of the join, or of several tables, fails with error 1235 (multi-table UPDATE writing a joined table / writing several tables). To write into the other table, reverse the order of the join.
• ORDER BY and LIMIT are not allowed as soon as there are several tables (1221).
• A row of the modified table that matches several rows of the join is modified only once, with the first match.
• The modified table can be an updatable view (see "Updatable views", chapter 7); a non-updatable view is rejected (1288).

Typical Errors#

8.3 DELETE#

Synopsis#

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM table [[AS] alias]
    [WHERE condition] [ORDER BY ...] [LIMIT n] [RETURNING item, ...]   -- 6.9

DELETE [IGNORE] target [, target ...] FROM table1 [join ...] [WHERE condition]
DELETE [IGNORE] FROM target [, target ...] USING table1 [join ...] [WHERE condition]

Simple DELETE#

INSERT INTO commandes (client_id, montant, date_cmd) VALUES
    (4, 12.00, '2026-10-07'), (7, 0.00, '2026-10-08'), (99, 5.00, '2026-10-08');

DELETE FROM commandes WHERE montant = 0;
-- OK, 1 row(s) affected
DELETE FROM clients WHERE solde IS NULL ORDER BY id LIMIT 1;
-- OK, 1 row(s) affected        (David)

DELETE FROM table without a condition empties the table; see §8.7 for the comparison with TRUNCATE.

Multi-Table DELETE#

Both forms delete rows from a join: the targets are named before FROM (first form) or after FROM and before USING (second form), by name or alias; the join that follows FROM (or USING) chooses the rows. Any table of the join can be a target, and several at once; a table of the join that is not named as a target serves only to filter.

-- orders whose customer no longer exists (anti-join)
DELETE o FROM commandes o
LEFT JOIN clients c ON c.id = o.client_id
WHERE c.id IS NULL;
-- OK, 2 row(s) affected        (orders of customers 4 and 99)

-- a customer from Oran and their orders, in one statement
DELETE c, o FROM clients c
JOIN commandes o ON o.client_id = c.id
WHERE c.ville = 'Oran';
-- OK, 2 row(s) affected        (Chloé and her order 4)

-- same form with USING: the target is the row of gros_clients
DELETE FROM g USING gros_clients g JOIN clients c ON c.id = g.client_id WHERE c.nom = 'Alice';
-- OK, 1 row(s) affected

Rules:

• All matches of the join are collected before the first deletion: a deleted row does not change the join in progress.
• A row that matches several times (customer with several orders) is deleted and counted only once; two aliases of the same table make only one target. The affected rows are the sum of the rows deleted from all targets.
• The targets are emptied in join order, each with its DELETE triggers and its foreign keys, like an ordinary DELETE; an error (for example 1451, parent row still referenced by a table that is not emptied) cancels the whole statement.
• A target absent from the join is an error (1109, unknown table).
• ORDER BY and LIMIT are not allowed in the multi-table form (1221), nor is RETURNING.
• DELETE privilege on each target, SELECT on the tables read.

Typical Errors#

8.4 Parameterized Queries#

Two parameter styles are accepted in the SQL text, in place of a literal value:

SELECT * FROM clients WHERE ville = ? AND solde > ?;
SELECT * FROM clients WHERE ville = :ville AND solde > :seuil;

The two styles are not mixed within a single statement. Parameters can appear anywhere an expression is expected: WHERE, SET, VALUES, LIMIT, etc. This is the recommended form for any value that comes from the user or the application (via the API, a network connector or miraj-cli), rather than building the SQL text by concatenation: bound values are never interpreted as SQL, which prevents injections and avoids manually reformatting literals (dates, strings to escape, numbers). A statement in which a parameter has not received a value fails (1210, Incorrect arguments to parameter 1).

EXECUTE ... USING (below) binds values to the ? parameters of a prepared statement, in order.

8.5 PREPARE, EXECUTE, DEALLOCATE PREPARE#

A prepared statement separates the parsing of the SQL text (done once by PREPARE) from its execution (done as many times as needed by EXECUTE), with ? parameters bound at each execution. This is the dynamic form of SQL, useful when the statement text is built by code (variable table name, generated query) rather than written once and for all.

Synopsis#

PREPARE name FROM expression

EXECUTE name [USING expression [, expression ...]]
EXECUTE IMMEDIATE expression [USING expression [, expression ...]]

{DEALLOCATE | DROP} PREPARE name

• The PREPARE expression is the text of the statement to prepare: a string literal, a user variable (@sql), or any expression that produces a string, evaluated when PREPARE runs. The text must contain exactly one statement (a trailing ; is tolerated).
• name is case-insensitive and designates the prepared statement for the subsequent EXECUTE and DEALLOCATE PREPARE, within the same session.
• Preparing again under a name already in use silently replaces the previous prepared statement.
• EXECUTE name USING ... binds the USING values, in order, to the ? parameters of the prepared text; their number must exactly match that of the parameters.
• EXECUTE IMMEDIATE expression prepares, executes then discards a statement in a single step, without giving it a name.
• DEALLOCATE PREPARE name (or DROP PREPARE name, synonyms) releases the prepared statement; subsequent EXECUTE under that name fail.

Lifecycle#

1. PREPARE parses the text and stores it under a name, for the current session.
2. EXECUTE (one or more times) binds the parameters and executes the already parsed statement.
3. DEALLOCATE PREPARE releases the preparation; it is also implicitly lost at the end of the session.

Complete Example#

PREPARE lire FROM 'SELECT nom FROM clients WHERE id = ?';
EXECUTE lire USING 1;      -- Alice
EXECUTE lire USING 2;      -- Bob
DEALLOCATE PREPARE lire;
EXECUTE lire USING 1;
-- ERROR 1243 (HY000): Unknown prepared statement handler (lire) given to EXECUTE

Dynamically built text (variable table name), a typical use case in a routine:

SET @table = 'clients';
SET @sql = CONCAT('UPDATE ', @table, ' SET solde = solde * (1 - ?) WHERE id = ?');
PREPARE remise FROM @sql;
EXECUTE remise USING 0.10, 2;      -- OK, 1 row(s) affected (Bob: 210.00 -> 189.00)
DEALLOCATE PREPARE remise;

EXECUTE IMMEDIATE, for a single-use statement:

EXECUTE IMMEDIATE CONCAT('SELECT COUNT(*) FROM ', @table);
EXECUTE IMMEDIATE 'INSERT INTO clients (nom) VALUES (?)' USING 'Hicham';

Typical Errors#

8.6 Additional Notes: INSERT, REPLACE, ON DUPLICATE KEY UPDATE#

The examples in this section and the following ones reuse the clients and commandes tables from the start of the chapter, recreated empty, with in addition a generated column nom_maj VARCHAR(50) AS (UPPER(nom)) STORED in clients and FOREIGN KEY (client_id) REFERENCES clients (id) in commandes.

Full Recognized Syntax#

{INSERT | REPLACE} [LOW_PRIORITY | DELAYED] [HIGH_PRIORITY] [IGNORE] [INTO] [database.]table
    [(column [, ...])]
    { {VALUES | VALUE} (expr | DEFAULT [, ...]) [, (...) ...] [AS alias [(column, ...)]]
    | SET column = expr [, ...]
    | (select_query) | select_query }
    [ON DUPLICATE KEY UPDATE column = expr [, ...]]      -- INSERT only
    [RETURNING item [, ...]]                           -- 6.9

• INTO is optional. VALUE is a synonym for VALUES. () as a column list (INSERT INTO t () VALUES ()) inserts an all-default row.
• LOW_PRIORITY, DELAYED and HIGH_PRIORITY are accepted and have no effect.
• IGNORE does not exist for REPLACE (REPLACE IGNORE: error 1064), nor does REPLACE ... ON DUPLICATE KEY UPDATE.
• The target table cannot carry an alias (INSERT INTO clients AS c ...: 1064).
• The SET column = expr form does not accept the DEFAULT keyword as a value (INSERT INTO clients SET solde = DEFAULT: error 1064); omitting the column has the same effect. DEFAULT remains allowed in VALUES (...) and in UPDATE ... SET col = DEFAULT.
• The query of an INSERT ... SELECT can start with WITH (INSERT INTO t WITH x AS (…) SELECT …); the WITH is not placed before INSERT.
• INSERT INTO t PARTITION (p) ...: Cluster edition only (9002 elsewhere).

Example: Insertion, Generated Column, Default Value#

INSERT INTO clients (nom, ville) VALUES ('Alice', 'Oran'), ('Bob', 'Alger'), ('Chloé', 'Oran');
-- OK, 3 row(s) affected

INSERT INTO clients (nom, nom_maj) VALUES ('Dan', 'IGNORE');   -- nom_maj value ignored
SELECT id, nom, nom_maj FROM clients;

+----+-------+---------+
| id | nom   | nom_maj |
+----+-------+---------+
|  1 | Alice | ALICE   |
|  2 | Bob   | BOB     |
|  3 | Chloé | CHLOÉ   |
|  4 | Dan   | DAN     |
+----+-------+---------+

INSERT INTO clients (nom, ville) VALUES ('a');
-- ERROR 1136 (21S01): Column count doesn't match value count at row 1
INSERT INTO clients (nom, inconnue) VALUES ('a', 'b');
-- ERROR 1054 (42S22): Unknown column 'inconnue' in 'field list'
INSERT INTO clients (nom) VALUES (NULL);
-- ERROR 1048 (23000): Column 'nom' cannot be null
INSERT INTO clients (ville) VALUES ('x');
-- ERROR 1364 (HY000): Field 'nom' doesn't have a default value
INSERT INTO clients (nom) VALUES (REPEAT('x', 60));
-- ERROR 1406 (22001): Data too long for column 'nom' at row 1

These rejections are those of strict mode (STRICT_TRANS_TABLES, the default). Outside strict mode (sql_mode without STRICT_TRANS_TABLES or STRICT_ALL_TABLES), a NULL explicitly written into a NOT NULL column by INSERT, REPLACE or ON DUPLICATE KEY UPDATE receives the type's implicit value (0, empty string, first value of an ENUM), never the declared DEFAULT, with warning 1048, even for a single-row statement (the reference keeps error 1048 in that case):

SET sql_mode = '';
INSERT INTO clients (nom) VALUES (NULL);   -- row inserted with nom = '', warning 1048

INSERT IGNORE: Exactly What Is Turned into a Warning#

With VALUES, SET and SELECT, IGNORE converts into a warning (row skipped or value corrected):

These remain errors even with IGNORE: number of values (1136), unknown column (1054), unknown table (1146), invalid date and NULL in a NOT NULL date column (no "zero date").

The 1364 warning for a NOT NULL column without DEFAULT absent from the column list (with IGNORE, outside strict mode, and for LOAD DATA even in strict mode) is given once per statement and per column, before the other warnings, whatever the number of rows (even none); each row receives the type's implicit value (a NOT NULL ENUM its first value, without a warning). A column that a BEFORE INSERT trigger references by NEW.col counts as supplied: neither error nor warning 1364, even in strict mode. A skipped row does not consume an AUTO_INCREMENT number. Foreign keys are checked row by row, in order: a row that references a row inserted later in the same statement is skipped, and the following ones are judged on the state it leaves.

INSERT IGNORE INTO commandes (client_id, montant) VALUES (99, 5), (3, 7);
-- OK, 1 row(s) affected
-- Warning: Cannot add or update a child row: a foreign key constraint fails
--   (`d`.`commandes`, CONSTRAINT `commandes_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`))

INSERT IGNORE INTO clients (id, nom) VALUES (1, 'Doublon'), (10, 'Nouveau');
-- OK, 1 row(s) affected
-- Warning: Duplicate entry '1' for key 'PRIMARY'

INSERT IGNORE INTO clients (nom) VALUES (NULL);
-- OK, 1 row(s) affected          (nom = empty string)
-- Warning: Column 'nom' cannot be null

Without IGNORE, a multi-row INSERT statement is all or nothing: the first error cancels all the rows of the statement, including the batches already produced by an INSERT ... SELECT. Foreign keys are checked row by row, in row order: a row can reference a row inserted before it by the same statement (or itself), never a row inserted later (error 1452, the whole statement is cancelled):

CREATE TABLE n (id INT PRIMARY KEY, sup INT, FOREIGN KEY (sup) REFERENCES n (id));
INSERT INTO n VALUES (1, NULL), (2, 1);   -- OK, 2 row(s) affected
INSERT INTO n VALUES (4, 3), (3, NULL);   -- ERROR 1452: row 3 does not exist yet
SELECT * FROM n;                          -- (1, NULL) and (2, 1) only

INSERT ... SELECT#

• The query can be written with or without parentheses, and can start with WITH.
• The result is inserted in batches of 4,096 rows; if the target table is also read by the query (including in a subquery or a derived table), the query is first read in full before the first insertion:

  INSERT INTO clients (nom) SELECT nom FROM clients;   -- OK: doubles the number of rows

• Source tables are locked for reading, the target table locked for writing during the whole read.
• IGNORE, ON DUPLICATE KEY UPDATE and REPLACE work with SELECT (ON DUPLICATE KEY UPDATE: no row alias AS alias after a SELECT).

REPLACE: Precise Behavior#

For each row, any existing row that conflicts on the primary key or on a UNIQUE key (one per key) is deleted, then the new row is inserted — never an in-place update. Columns not named therefore take their default value (example in §8.1).

Affected rows = deleted rows + inserted rows (child rows deleted or modified by ON DELETE do not count). Under RESTRICT / NO ACTION, a referenced parent row returns 1451 and the whole statement is cancelled. Conversions are checked before any write; the foreign keys of each inserted row as soon as it is written, on the state left by the previous rows. REPLACE always runs in a transaction. Triggers: BEFORE INSERT, then BEFORE / AFTER DELETE around each replaced row, then AFTER INSERT.

ON DUPLICATE KEY UPDATE: Precise Behavior#

• The first conflicting row is updated: primary key first, then UNIQUE keys in definition order (rows written earlier by the same statement included). Assignments are evaluated left to right.
• Affected rows: 1 per inserted row, 2 per existing row actually modified, 0 per existing row left as is; the total is the sum.
• VALUES(col) returns the value proposed for col in this clause (including in a subquery of the clause); elsewhere, VALUES(col) is NULL. VALUE(col) is a synonym (qualified table included: VALUE(t2.b)), with one difference: in a subquery of the clause, it is NULL when col designates a column of a table of the subquery. An unknown column or one from another table returns 1054; VALUES(col) and VALUE(col) are rejected in a DEFAULT, a CHECK and a generated column. The row alias (VALUES (...) AS nouvelle or AS nouvelle (a, b, c), which also renames the columns) is the recommended form.
• An updated row does not consume an AUTO_INCREMENT value; LAST_INSERT_ID() returns the first generated value, otherwise the AUTO_INCREMENT value of the last conflicting row.
• The clause can contain subqueries, correlated or not with the conflicting row (b = (SELECT v + 100 FROM u WHERE u.id = t2.a)).
• With IGNORE, errors 1062, 1452, 1451 and 1048 of the update part become warnings and the row stays unchanged. ON DUPLICATE KEY UPDATE always runs in a transaction.
• ON UPDATE CURRENT_TIMESTAMP columns are timestamped as in an UPDATE; STORED generated columns and CHECK constraints are recomputed / checked on the modified row.

Example (table t2 (a INT PRIMARY KEY, b INT) containing (1,1) and (2,2)):

INSERT INTO t2 VALUES (1,10),(3,30),(2,20) ON DUPLICATE KEY UPDATE b = VALUES(b);
-- OK, 5 row(s) affected      (2 + 1 + 2)
SELECT * FROM t2 ORDER BY a;       -- (1,10) (2,20) (3,30)

INSERT INTO t2 VALUES (1,10) ON DUPLICATE KEY UPDATE b = b;
-- OK, 0 row(s) affected      (nothing changes)

INSERT INTO t2 VALUES (1, 5) AS nv (x, y) ON DUPLICATE KEY UPDATE b = b + nv.y;
-- OK, 2 row(s) affected      (b: 10 -> 15)

AUTO_INCREMENT and LAST_INSERT_ID()#

On a multi-row INSERT, LAST_INSERT_ID() returns the first value generated by the statement. A row skipped by IGNORE, or updated by ON DUPLICATE KEY UPDATE, does not consume a number.

8.7 Additional Notes: UPDATE and DELETE#

Affected Rows#

The number of rows affected by an UPDATE is that of the rows selected by the WHERE, whether they change or not:

CREATE TABLE u (id INT PRIMARY KEY, v INT);
INSERT INTO u VALUES (1,1),(2,2),(3,3);
UPDATE u SET v = v WHERE id <= 2;               -- OK, 2 row(s) affected
UPDATE u SET v = 0 ORDER BY id DESC LIMIT 1;    -- OK, 1 row(s) affected

Modifiers: LOW_PRIORITY, QUICK, IGNORE#

UPDATE [LOW_PRIORITY] [IGNORE] table ...
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] ...

LOW_PRIORITY and QUICK are accepted and have no effect. Without IGNORE, the first offending row makes the whole statement fail:

UPDATE u SET id = 1 WHERE id = 3;
-- ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

UPDATE IGNORE: a row whose write violates a primary key or UNIQUE (1062), a foreign key (1451 / 1452), a CHECK constraint or the partitioning is left as it is, with a warning; conversions are done as outside strict mode (value clamped or implicit, 1048, 1264, 1265, 1366 as warnings). The rows left do not fire AFTER UPDATE and do not count in the affected rows. Rows are written one by one, each seeing the previous ones:

DELETE FROM u;
INSERT INTO u VALUES (1,1),(2,2),(3,3);
UPDATE IGNORE u SET id = id + 1;
-- OK, 1 row(s) affected
-- Warning: Duplicate entry '2' for key 'PRIMARY'
-- Warning: Duplicate entry '3' for key 'PRIMARY'
SELECT * FROM u;                     -- (1,1) (2,2) (4,3): only row 3 could move to 4

UPDATE IGNORE u SET v = 'abc' WHERE id = 1;
-- OK, 1 row(s) affected          (v = 0)
-- Warning: Incorrect integer value: 'abc' for column `d`.`u`.`v` at row 1

DELETE IGNORE: a row whose deletion would fail on a foreign key (1451, cascades included: the parent row and its children stay together) is left with a warning, without AFTER DELETE; the other rows are deleted. Valid for the simple DELETE as for the multi-table one.

-- customers 2 and 4; only customer 2 has orders
DELETE IGNORE FROM clients WHERE id IN (2, 4);
-- OK, 1 row(s) affected
-- Warning: Cannot delete or update a parent row: a foreign key constraint fails (...)

SET: Expressions, DEFAULT, Subqueries#

UPDATE clients SET solde = DEFAULT WHERE id = 2;            -- the column's default value
UPDATE u SET v = (SELECT COUNT(*) FROM u);                  -- subquery, read before the write
DELETE FROM clients WHERE id IN (SELECT id FROM clients WHERE solde < 0);

The values and target rows are computed before the first write: a subquery that reads the modified table sees the state from before the statement. The ORDER BY of a single-table UPDATE or DELETE can contain subqueries.

An assigned column can be qualified by the table and the database (UPDATE ventes.cible SET ventes.cible.n = 1, UPDATE cible c SET ventes.c.n = 1), in a single- or multi-table UPDATE as in ON DUPLICATE KEY UPDATE: the qualifier must designate the modified table (by its alias if there is one, database included), otherwise error 1054 quotes the full name as written ("Unknown column 'autre.cible.n' in 'field list'").

Generated columns: never assignable (explicit value ignored, except DEFAULT); STORED columns recomputed; ON UPDATE CURRENT_TIMESTAMP applied when the row actually changes. CHECK constraints checked on the full row (error 4025). BEFORE / AFTER UPDATE triggers run for all selected rows, even unchanged ones. Outside strict mode, a NULL assigned to a NOT NULL column becomes the type's implicit value, with warning 1048.

DELETE Without a Condition and TRUNCATE#

DELETE FROM table without WHERE, ORDER BY or LIMIT deletes all rows and keeps the AUTO_INCREMENT counter. Outside a transaction, with no trigger or foreign key involved, it is executed in a single block (without a row-by-row journal). DELETE triggers run row by row, including for a DELETE without a condition.

TRUNCATE [TABLE] table also empties the table, but in a single definition operation (see TRUNCATE TABLE, chapter 7):

CREATE TABLE j (id INT AUTO_INCREMENT PRIMARY KEY, t VARCHAR(5));
INSERT INTO j (t) VALUES ('a'), ('b');
DELETE FROM j;                              -- OK, 2 row(s) affected
INSERT INTO j (t) VALUES ('c');             -- id = 3
TRUNCATE TABLE j;
INSERT INTO j (t) VALUES ('d');             -- id = 1

Foreign Keys#

The ON DELETE / ON UPDATE actions apply (see DDL): CASCADE and SET NULL modify the child rows without counting them in the affected rows; RESTRICT / NO ACTION reject with 1451. Child rows modified by a cascade fire neither a trigger nor the ON UPDATE CURRENT_TIMESTAMP timestamp.

Constraints are checked immediately, row by row, in the order in which the statement processes its rows (table read order, or ORDER BY), and not on the final state: a parent row cannot leave its key as long as a row still present references it, even if the statement would have removed it later. This matters mostly for a table that references itself:

CREATE TABLE arbre (id INT PRIMARY KEY, parent INT, FOREIGN KEY (parent) REFERENCES arbre (id));
INSERT INTO arbre VALUES (1, NULL), (2, 1), (3, 2);
DELETE FROM arbre WHERE id >= 2;                   -- ERROR 1451: row 3 still references 2
UPDATE arbre SET id = id + 10;                     -- ERROR 1451: row 2 still references 1
DELETE FROM arbre WHERE id >= 2 ORDER BY id DESC;  -- OK, 2 row(s) affected: leaves first

A modified row sees the rows already processed with their new values (itself included) and the following ones with their old values. The CASCADE and SET NULL actions of a statement are planned over the whole statement before any write. SET foreign_key_checks = 0 suspends checks and actions for the session.

DELETE FROM clients WHERE id = 2;
-- ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
--   (`d`.`commandes`, CONSTRAINT `commandes_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`))

Multi-Table UPDATE / DELETE: Rules Recap#

UPDATE clients c JOIN commandes o ON o.client_id = c.id SET o.montant = 0;
-- ERROR 1235 (42000): This version of Miraj doesn't yet support 'multi-table UPDATE writing a joined table'
UPDATE clients c JOIN commandes o ON o.client_id = c.id SET c.solde = 0 LIMIT 1;
-- ERROR 1221 (HY000): Incorrect usage of UPDATE and LIMIT
DELETE o FROM commandes o JOIN clients c ON c.id = o.client_id WHERE c.id = 2 ORDER BY o.id;
-- ERROR 1221 (HY000): Incorrect usage of DELETE and ORDER BY
DELETE x FROM commandes o JOIN clients c ON c.id = o.client_id;
-- ERROR 1109 (42S02): Unknown table 'x' in MULTI DELETE

To write into the second table of an UPDATE, reverse the order of the join (the table to modify first).

Deferred Update#

An UPDATE of a single row designated by primary key or UNIQUE can be re-evaluated at COMMIT on the last committed value (deferred_update variable, ON by default); two transactions that update the same row this way then both commit instead of failing with 1213. UPDATE IGNORE is never deferred. See Transactions and Concurrency.

Values Drawn from a Sequence#

A sequence (chapter 7, "Sequences") can supply the value of a column, either through a DEFAULT on the column, or directly in the statement:

CREATE SEQUENCE seq_facture START WITH 1000;
CREATE TABLE facture (n BIGINT DEFAULT NEXTVAL(seq_facture) PRIMARY KEY, client INT);
INSERT INTO facture (client) VALUES (7), (8);                             -- n = 1000, 1001
INSERT INTO facture (n, client) VALUES (NEXT VALUE FOR seq_facture, 9);   -- n = 1002
SELECT LASTVAL(seq_facture);                                              -- 1002, last value of the session

A value served is never given back: a ROLLBACK leaves a gap in the numbering. Unlike AUTO_INCREMENT, a sequence can feed several tables. INSERT, UPDATE, DELETE and TRUNCATE directly targeting a sequence return error 4093; to reposition it, use SETVAL or ALTER SEQUENCE … RESTART. SELECT * FROM seq_facture reads its state (next_not_cached_value, increment…).

Versioned Tables and Application Periods#

On a WITH SYSTEM VERSIONING table (chapter 12), UPDATE, DELETE, REPLACE and INSERT … ON DUPLICATE KEY UPDATE keep the old image of the row with its end date; writes are used exactly as on an ordinary table, and a ROLLBACK also undoes the history. DELETE HISTORY FROM table [BEFORE SYSTEM_TIME time] deletes finished versions, without touching the current rows. The row_start and row_end columns are written by MIRAJ: any value given by the statement is overwritten.

On an application-period table (PERIOD FOR), UPDATE table FOR PORTION OF period FROM a TO b SET … and DELETE FROM table FOR PORTION OF period FROM a TO b … modify or delete only the part of each row that falls within the interval, the pieces outside the interval being reinserted (chapter 23, §23.7).

8.8 LOAD DATA INFILE and LOAD XML#

Syntax#

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file'
    [REPLACE | IGNORE]
    INTO TABLE [database.]table
    [PARTITION (p, ...)]                                  -- Cluster edition
    [CHARACTER SET charset]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'character']
        [ESCAPED BY 'character']]
    [LINES [STARTING BY 'string'] [TERMINATED BY 'string']]
    [IGNORE n {LINES | ROWS}]
    [(column | @variable [, ...])]
    [SET column = expression | DEFAULT [, ...]]

Default values: fields separated by a tab, lines terminated by \n, escape \ (\N represents NULL), no delimiter.

Server File and LOCAL File#

The file is kept in memory for the duration of the statement. Character sets: utf8mb4 (default), utf8mb3, latin1, ascii, binary; a binary column receives the file's bytes as they are. Delimiter and escape: one character at most (error 1083). The fixed-width format (empty separator and delimiter) is not supported (1235). LOAD XML is described below.

Behavior#

LOAD DATA behaves like an INSERT: same privileges (INSERT), same triggers, foreign keys, generated columns, AUTO_INCREMENT and integration into the transaction (can be undone by ROLLBACK). The statement returns the number of rows loaded and, except for REPLACE, the message Records: n Deleted: 0 Skipped: n Warnings: n of the OK packet.

• IGNORE n LINES skips the first n lines (header).
• Column list: absent columns receive their default value; @variable captures a field without loading it (usable in SET, and kept in the session: the variable holds the field of the last row).
• SET col = expr assigns a column from the variables and the columns already loaded; col = DEFAULT applies the default value.
• A \N field (or the unenclosed word NULL) is NULL; a NULL between delimiters ("NULL") is the text.
• Duplicates: error 1062 (everything cancelled), IGNORE (row skipped, warning), REPLACE (conflicting row replaced).
• Row too short (1261, one warning or error per missing column) or too long (1262), NULL in a NOT NULL column (1048, or implicit value with 1263), empty field in an integer column (1366): errors in strict mode, warnings with IGNORE, with LOCAL or outside strict mode. Columns without a field in a row that is too short receive their default value when it is constant, otherwise the type's implicit value.
• A LOAD DATA statement is rejected in a routine (1314).

Runnable Example#

File clients.csv in the secure_file_priv folder (CRLF line endings):

nom,solde
"Benali, Amine",31
"Le ""Grand""",12.5
Hicham,\N

CREATE TABLE cl (id INT AUTO_INCREMENT PRIMARY KEY, nom VARCHAR(30), solde DECIMAL(8,2) DEFAULT 0);

LOAD DATA INFILE 'clients.csv' INTO TABLE cl
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\r\n'
    IGNORE 1 LINES
    (nom, solde);
-- OK, 3 row(s) affected

SELECT * FROM cl;

+----+---------------+-------+
| id | nom           | solde |
+----+---------------+-------+
|  1 | Benali, Amine | 31.00 |
|  2 | Le "Grand"    | 12.50 |
|  3 | Hicham        |  NULL |
+----+---------------+-------+

With variables and SET (file p.txt, fields separated by ;, decimal commas):

1;amine;3,50;x
2;sara;12,00;y

CREATE TABLE p (id INT PRIMARY KEY, nom VARCHAR(20), prix DECIMAL(8,2), note VARCHAR(10) DEFAULT 'n/a');

LOAD DATA INFILE 'p.txt' INTO TABLE p FIELDS TERMINATED BY ';'
    (id, @nom, @prix, @ignore)
    SET nom = UPPER(@nom), prix = REPLACE(@prix, ',', '.'), note = DEFAULT;
-- OK, 2 row(s) affected

SELECT * FROM p;

+----+-------+-------+------+
| id | nom   | prix  | note |
+----+-------+-------+------+
|  1 | AMINE |  3.50 | n/a  |
|  2 | SARA  | 12.00 | n/a  |
+----+-------+-------+------+

SELECT @ignore then returns y, the field of the last row. Reloading the same file:

LOAD DATA INFILE 'p.txt' INTO TABLE p FIELDS TERMINATED BY ';' (id, @nom, @prix, @ignore);
-- ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'     (nothing is loaded)
LOAD DATA INFILE 'p.txt' IGNORE INTO TABLE p FIELDS TERMINATED BY ';' (id, @nom, @prix, @ignore);
-- two 1062 warnings, existing rows unchanged
LOAD DATA INFILE 'p.txt' REPLACE INTO TABLE p FIELDS TERMINATED BY ';' (id, @nom, @prix, @ignore) SET nom = @nom;
-- OK, 4 row(s) affected      (2 deleted + 2 inserted; prix NULL, note 'n/a')

Without a configured secure_file_priv folder, or with a missing file:

ERREUR 1290 (HY000) : The server is running with the --secure-file-priv option so it cannot execute this statement
ERREUR 29 (HY000) : File 'absent.txt' not found (Errcode: 2 "No such file or directory")

LOAD XML#

LOAD XML [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file'
    [REPLACE | IGNORE]
    INTO TABLE [database.]table [PARTITION (p, ...)]
    [CHARACTER SET charset]
    [ROWS IDENTIFIED BY '<tag>']
    [IGNORE n {LINES | ROWS}]
    [(column | @variable, ...)]
    [SET column = expression | DEFAULT, ...]

Each <tag> element of the document (<row> by default, at any depth) is a row. Its values are named and can take three forms, which can be mixed within a single row:

• Each destination (all the table's columns, or the (column | @variable, ...) list) takes the value of the same name (case-insensitive); a value with no destination is ignored; a column with no value in the row keeps its default value (no 1261/1262).
• xsi:nil="true" is NULL; an empty element (<nom/>, <nom></nom>) is the empty string.
• IGNORE n ROWS (or LINES) skips the first n XML rows.
• Entities < > & " ', references é / é, sections <![CDATA[...]]>, comments, prolog <?xml ...?> and <!DOCTYPE ...> (ignored) are understood. The file is read in the statement's CHARACTER SET (UTF-8 by default), the prolog's encoding is ignored.
• Server file or LOCAL, privileges, REPLACE / IGNORE, SET clause, conversions, triggers, warnings and Records: ... message: as for LOAD DATA. Rejected in a routine (1314).
• Malformed document (unclosed or crossed tag, unknown entity, unquoted attribute...): error 9085 with the line number, nothing is loaded.

CREATE TABLE px (id INT PRIMARY KEY, nom VARCHAR(20), ville VARCHAR(20) DEFAULT 'Alger');

-- p.xml:
-- <resultset xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
--   <row id="1" nom="Amine"/>
--   <row><id>2</id><nom>Sara</nom><ville>Oran</ville></row>
--   <row><field name="id">3</field><field name="nom" xsi:nil="true" /></row>
-- </resultset>
LOAD XML INFILE 'p.xml' INTO TABLE px;
-- OK, 3 row(s) affected   Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

SELECT * FROM px;

+----+-------+-------+
| id | nom   | ville |
+----+-------+-------+
|  1 | Amine | Alger |
|  2 | Sara  | Oran  |
|  3 | NULL  | Alger |
+----+-------+-------+

The reverse export, SELECT ... INTO OUTFILE 'file' [FIELDS ...] [LINES ...] and SELECT ... INTO DUMPFILE 'file', writes to the same folder; an existing file is never replaced (error 1086). See SELECT Queries, §9.23.

8.9 RETURNING and Other Unsupported Syntaxes#

INSERT, REPLACE and DELETE ... RETURNING#

INSERT [IGNORE] INTO table ... [ON DUPLICATE KEY UPDATE ...] RETURNING item [, item ...]
REPLACE INTO table ... RETURNING item [, item ...]
DELETE FROM table [WHERE ...] [ORDER BY ...] [LIMIT n] RETURNING item [, item ...]

The write returns, as a result set, one row per row written, in the order of the writes. All source forms are allowed (VALUES, SET, SELECT). An item is the same as in a SELECT list: *, table.*, column, expression, function call, with or without alias (AS optional); a column without an alias is named by the text of its expression.

Values returned:

• INSERT / REPLACE: values actually written — assigned AUTO_INCREMENT value, DEFAULT, generated columns, changes made by a BEFORE INSERT trigger;
• ON DUPLICATE KEY UPDATE: the inserted row, or the conflicting row as it is after its update (also returned when the update changes nothing);
• INSERT IGNORE: only the rows actually inserted (a row skipped because of a duplicate is not returned);
• DELETE: each deleted row, values from before the deletion (WHERE, ORDER BY and LIMIT choose the rows as usual).

Examples (table r (id INT AUTO_INCREMENT PRIMARY KEY, nom VARCHAR(20), solde DECIMAL(8,2) DEFAULT 0), initially empty):

INSERT INTO r (nom) VALUES ('Ali'), ('Lina') RETURNING id, nom, solde;

+----+------+-------+
| id | nom  | solde |
+----+------+-------+
|  1 | Ali  |  0.00 |
|  2 | Lina |  0.00 |
+----+------+-------+

INSERT INTO r (id, nom) VALUES (1, 'Ali B.')
ON DUPLICATE KEY UPDATE nom = VALUES(nom)
RETURNING id, UPPER(nom) AS nom_maj;                 -- 1 | ALI B.

INSERT IGNORE INTO r (id, nom) VALUES (1, 'x'), (3, 'Sami') RETURNING id, nom;   -- 3 | Sami

DELETE FROM r WHERE id >= 2 ORDER BY id LIMIT 10 RETURNING id, nom;

+----+------+
| id | nom  |
+----+------+
|  2 | Lina |
|  3 | Sami |
+----+------+

The result set is transmitted like that of a SELECT, in the text protocol as in a prepared query (COM_STMT_PREPARE describes its columns, COM_STMT_EXECUTE returns the rows in the binary protocol); in a stored procedure, it goes up to the client like that of a SELECT in the body. LAST_INSERT_ID() and ROW_COUNT() are kept up to date as without the clause. In a transaction, a ROLLBACK erases the rows already returned.

Privileges: in addition to the write privilege, SELECT on the table, or on each of the columns returned (all those of the table for *); otherwise, 1142 or 1143.

Rejected:

Not supported by MIRAJ: through a view and on a partitioned table of the Cluster edition (1235). A system-versioned table is allowed (DELETE returns the removed row, its historical version is kept); a sequence rejects DELETE as without the clause (4093).

RETURNING is a reserved word: a table, column or alias of that name is written in backticks (`returning`).

Other Unsupported Syntaxes#

Example:

UPDATE t SET n = 0 WHERE id > 40 RETURNING id;
-- ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds
--   to your Miraj server version for the right syntax to use near 'RETURNING id' ...

8.10 DML Error Summary#