UPDATE et DELETE à cheval sur deux tables

La forme qui tourne partout

La clause WHERE d’un UPDATE est une clause WHERE ordinaire : une sous-requête peut donc choisir les lignes. Aucun moteur n’a besoin de quoi que ce soit de particulier pour cela.

-- Works on every engine in this list, unchanged.
UPDATE customers
SET loyalty_points = loyalty_points + 50
WHERE id IN (
  SELECT customer_id FROM orders WHERE status = 'refunded' AND total > 2000
);

Quand la nouvelle valeur vient elle-même de l’autre table, une sous-requête corrélée dans SET s’en charge aussi :

-- The same shape when the new value comes from the other table.
UPDATE products
SET stock_quantity = stock_quantity + (
  SELECT COALESCE(SUM(change_qty), 0)
  FROM inventory_logs il
  WHERE il.product_id = products.id
    AND il.reason = 'return'
)
WHERE id IN (SELECT product_id FROM inventory_logs WHERE reason = 'return');

Notez la condition répétée. La sous-requête du SET décide de la valeur, celle du WHERE décide quelles lignes sont touchées — sans la seconde, tous les produits seraient mis à jour, et ceux sans entrée de journal correspondante seraient fixés via COALESCE(..., 0) : rien n’est ajouté, mais la ligne est écrite quand même.

Cette forme est portable et lisible. Elle n’est pas toujours la plus rapide : un moteur peut exécuter la sous-requête corrélée une fois par ligne, et c’est ce que le plan vous dira. Les syntaxes de jointure ci-dessous existent parce que, sur de grosses mises à jour, cette différence vaut la perte de portabilité.

MySQL et MariaDB

-- The join goes between UPDATE and SET.
UPDATE customers c
  JOIN orders o ON o.customer_id = c.id
SET c.loyalty_points = c.loyalty_points + 10
WHERE o.status = 'refunded';

-- DELETE names which table's rows to remove, before FROM.
DELETE c
FROM customers c
  JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'refunded';

La jointure se place entre UPDATE et SET, contrairement à tous les autres moteurs présentés ici. DELETE exige que la cible soit nommée avant FROM — DELETE c FROM customers c JOIN ... — car avec deux tables jointes, l’instruction serait sinon ambiguë sur les lignes à supprimer. Omettre cet alias est une erreur de syntaxe, ce qui est le bon cas ; en nommer un mauvais ne l’est pas.

PostgreSQL

-- The second table goes in FROM, and the join condition in WHERE.
UPDATE customers
SET loyalty_points = loyalty_points + 10
FROM orders o
WHERE o.customer_id = customers.id
  AND o.status = 'refunded';

-- DELETE uses USING rather than FROM for the extra table.
DELETE FROM wishlists
USING products p
WHERE p.id = wishlists.product_id
  AND p.stock_quantity = 0;

La table supplémentaire va dans FROM et la condition de jointure passe dans WHERE. Deux détails se ratent facilement : la table cible n’est pas répétée dans FROM (sinon vous la joignez à elle-même), et DELETE utilise USING là où UPDATE utilise FROM.

SQLite a adopté UPDATE … FROM avec la sémantique de PostgreSQL en 3.33 (2020), c’est pourquoi le troisième exemple de cette page s’exécute ici :

-- SQLite 3.33 (2020) and later, with PostgreSQL's spelling.
UPDATE customers
SET loyalty_points = loyalty_points + 10
FROM orders o
WHERE o.customer_id = customers.id
  AND o.status = 'refunded'
  AND o.total > 2500;

SQLite n’a pas de DELETE … USING : les suppressions passent donc toujours par une sous-requête.

SQL Server

-- The alias is updated, and the FROM clause repeats the target table.
UPDATE c
SET c.loyalty_points = c.loyalty_points + 10
FROM customers c
  JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'refunded';

DELETE c
FROM customers c
  JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'refunded';

C’est l’alias qui est mis à jour, et FROM répète la table cible pour donner une définition à cet alias. Cela se lit étrangement — la table est nommée deux fois pour une seule instruction — mais c’est la seule forme que T-SQL accepte, et DELETE suit le même modèle.

Oracle

-- Oracle has no UPDATE ... FROM and no UPDATE ... JOIN.
-- EXISTS is the portable answer, and the one to use.
UPDATE customers c
SET c.loyalty_points = c.loyalty_points + 10
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id AND o.status = 'refunded'
);

DELETE FROM wishlists w
WHERE EXISTS (
  SELECT 1 FROM products p
  WHERE p.id = w.product_id AND p.stock_quantity = 0
);

Oracle n’a ni UPDATE … JOIN ni UPDATE … FROM. EXISTS avec une sous-requête corrélée est la réponse habituelle, et c’est la même instruction que celle qui tourne sur les quatre autres moteurs.

Il existe une seconde option, la vue jointe modifiable — UPDATE (SELECT a.col, b.col FROM a JOIN b ON …) SET … — mais Oracle ne l’accepte que s’il peut prouver que la jointure préserve les clés, ce qui suppose une contrainte d’unicité ou une clé primaire sur la colonne jointe. Quand il ne le peut pas, il lève ORA-01779, et la solution est de revenir à EXISTS.

Avant d’exécuter l’une d’elles

Ce sont ces instructions qui font que Planche réécrit les UPDATE et DELETE en SELECT équivalents avant de les exécuter : une jointure rend l’ensemble affecté bien plus difficile à déduire à la lecture qu’un simple WHERE. Activez Paramètres › Sécurité › Aperçu des lignes affectées et vous obtenez d’abord le compte et un échantillon. La réécriture comprend les cinq formes ci-dessus, et le dépôt contient un script qui vérifie le compte déduit contre les lignes réellement modifiées sur MySQL, PostgreSQL, SQL Server, SQLite et Oracle.