6. DML Language

This chapter describes the data manipulation statements (DML): INSERT, UPDATE, DELETE, parameterized queries and prepared statements (PREPARE / EXECUTE / DEALLOCATE PREPARE).

The examples rely on two tables used throughout this chapter:

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
);

6.1 INSERT#

Synopsis#

INSERT [IGNORE] INTO table [(colonne, ...)]
    VALUES (valeur, ...) [, (valeur, ...) ...]
    | SET colonne = valeur [, colonne = valeur ...]
    | requête_select
    [ON DUPLICATE KEY UPDATE colonne = valeur [, colonne = valeur ...]]

REPLACE INTO table [(colonne, ...)]
    VALUES (valeur, ...) [, (valeur, ...) ...]
    | requête_select

Inserting One Row or Several Rows#

INSERT INTO clients (nom, ville) VALUES ('Alice', 'Oran');

INSERT INTO clients (nom, ville, solde) VALUES
    ('Bob', 'Alger', 50.00),
    ('Chloé', 'Oran', 0),
    ('David', 'Constantine', NULL);

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 row can be empty (VALUES ()) when the table has only columns with default values or nullable columns. A value can be DEFAULT, which explicitly means the column's default value:

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

INSERT ... SELECT#

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

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;

INSERT IGNORE turns into warnings the errors that would otherwise make the row fail (value too long, NOT NULL violated, duplicate key): the offending row is skipped, the others are inserted.

AUTO_INCREMENT#

An AUTO_INCREMENT column automatically receives the next value when the insertion does not give it one (column absent from the list, or explicit NULL):

INSERT INTO clients (nom) VALUES ('Grace');
SELECT LAST_INSERT_ID();   -- value assigned to the row that was just inserted

Explicitly giving a value greater than the current counter advances it accordingly; subsequent insertions restart beyond that value.

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.

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.

REPLACE#

REPLACE INTO inserts a row like INSERT, but if it conflicts with an existing row on a 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):

REPLACE INTO clients (id, nom, ville) VALUES (1, 'Alice', 'Alger');

REPLACE also accepts a SELECT source:

REPLACE INTO clients (id, nom) SELECT id + 100, nom FROM clients WHERE ville = 'Oran';

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 new DELETE/INSERT trigger firing for it) and only the columns named in ON DUPLICATE KEY UPDATE are modified.

INSERT INTO clients (id, nom, solde) VALUES (1, 'Alice', 100)
ON DUPLICATE KEY UPDATE solde = solde + VALUES(solde);

VALUES(column) designates, in the ON DUPLICATE KEY UPDATE clause, the value that the conflicting row would have received on insertion. A row inserted with VALUES ... AS alias can also be referenced by its alias:

INSERT INTO clients (id, nom, solde) VALUES (1, 'Alice', 100) AS nouvelle
ON DUPLICATE KEY UPDATE solde = solde + nouvelle.solde;

Typical Errors#

6.2 UPDATE#

Synopsis#

UPDATE table [[AS] alias]
    SET colonne = valeur [, colonne = valeur ...]
    [WHERE condition]
    [ORDER BY ...] [LIMIT n]

UPDATE table1 [[AS] alias1]
    {[INNER] JOIN | CROSS JOIN | LEFT [OUTER] JOIN | RIGHT [OUTER] JOIN} table2 [[AS] alias2]
        {ON condition | USING (colonne, ...)}
    [...autres jointures...]
    SET colonne = valeur [, colonne = valeur ...]
    [WHERE condition]

Single-Table UPDATE#

UPDATE clients SET solde = solde + 20 WHERE ville = 'Oran';

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

UPDATE clients SET solde = 0 WHERE solde < 0 ORDER BY id LIMIT 1;

Multi-Table UPDATE (Update with a Join)#

UPDATE accepts the same join types as a SELECT (see chapter 7): JOIN / INNER JOIN, CROSS JOIN, 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 one named in the SET clause. 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 = 42;

With USING, when both tables have a column of the same name:

UPDATE clients c
LEFT JOIN commandes o USING (id)
SET c.solde = c.solde + 1
WHERE o.id IS NULL;   -- customers without order no. 1..N (depending on the join)

With a derived table as the source of the values:

UPDATE clients c
CROSS 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;

Restrictions specific to multi-table UPDATE (checked by the engine):

• SET can only assign columns of the table actually modified; assigning a column of another table of the join fails.
• ORDER BY and LIMIT are not allowed as soon as there are several tables.
• The modified table cannot be a non-updatable view.

Typical Errors#

6.3 DELETE#

Synopsis#

DELETE FROM table [WHERE condition] [ORDER BY ...] [LIMIT n]

DELETE table1 [, table2 ...] FROM table1 [JOIN ...] [WHERE condition]
DELETE FROM table1 USING table1 [JOIN ...] [WHERE condition]

Simple DELETE#

DELETE FROM commandes WHERE montant IS NULL;
DELETE FROM clients WHERE solde = 0 ORDER BY id LIMIT 1;
DELETE FROM commandes;   -- empties the table (one row at a time, unlike TRUNCATE)

Multi-Table DELETE#

Two equivalent forms delete rows from one or several tables of a join, using the other joined tables only for filtering:

-- table(s) to empty named before FROM
DELETE c FROM clients c
JOIN commandes o ON o.client_id = c.id
WHERE o.montant < 0;

-- table(s) to empty named after USING
DELETE FROM c USING clients c
JOIN commandes o ON o.client_id = c.id
WHERE o.montant < 0;

ORDER BY and LIMIT are not allowed in the multi-table form. A table named before FROM (or after USING) that is not part of the join is an error (unknown table); a table of the join that is not listed as a target does not have its rows deleted.

Typical Errors#

6.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).

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

6.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 nom FROM expression

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

{DEALLOCATE | DROP} PREPARE nom

• 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).
• nom 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 nom 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 nom (or DROP PREPARE nom, 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 3;      -- 'Chloé'
DEALLOCATE PREPARE lire;
EXECUTE lire USING 1;      -- error 1243: unknown prepared statement

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, 1;
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#