UPDATE / DELETE avec sous-requêtes et JOIN

Les données qu'on utilise — stock et stock_in

Cet article couvre un style d'écriture plus avancé — mettre à jour et supprimer en s'appuyant sur les valeurs d'une autre table ou sur un résultat d'agrégation.

Précisément, trois schémas : des mises à jour dont la valeur est calculée par une sous-requête corrélée (une sous-requête dont le SELECT interne référence des colonnes de la ligne externe, donc évaluée ligne par ligne), des mises à jour en jointure via UPDATE … FROM contre une autre table, et des instructions DELETE avec une sous-requête dans la clause WHERE.

Avant les exercices, jette un œil aux définitions de colonnes et aux données d'exemple des deux tables — stock et stock_in.

① Exécute PRAGMA table_info(stock); pour vérifier les noms de colonnes, les types et la clé primaire de stock.

② Prévisualise les deux tables avec SELECT * FROM stock LIMIT 5; et SELECT * FROM stock_in;. Remarque que stock_in contient un sku qui n'existe pas côté stock.

Utiliser une sous-requête corrélée pour mettre à jour avec des valeurs d'une autre table

Écrire UPDATE table SET col = (SELECT ... WHERE sous_requete.cle = table.cle) évalue le SELECT interne une fois par ligne cible et écrit le résultat dans la colonne.

Comme le SELECT interne référence une colonne de la cible externe UPDATE (stock.sku), on parle de sous-requête corrélée.

Le piège : pour les lignes de stock sans correspondance dans stock_in, le SELECT interne renvoie NULL.

-- Sans COALESCE : qty est écrasé par NULL pour les sku sans correspondance
UPDATE stock
SET qty = qty + (SELECT SUM(add_qty) FROM stock_in WHERE stock_in.sku = stock.sku);

Si tu exécutes qty = qty + (SELECT ...) tel quel, les lignes sans arrivage voient leur qty écrasé par NULL.

Pour éviter ça, soit tu enveloppes la sous-requête dans COALESCE(sous_requete, 0) pour transformer NULL en 0, soit tu restreins la cible avec WHERE EXISTS (...) pour ne toucher que les lignes ayant effectivement un arrivage.

-- D'abord prévisualise les valeurs après mise à jour avec SELECT (stock n'est pas modifié)
SELECT sku, qty,
  qty + (SELECT COALESCE(SUM(add_qty), 0) FROM stock_in WHERE stock_in.sku = stock.sku) AS new_qty
FROM stock;

-- Une fois satisfait, bascule en UPDATE (COALESCE convertit NULL en 0)
UPDATE stock
SET qty = qty + (SELECT COALESCE(SUM(add_qty), 0) FROM stock_in WHERE stock_in.sku = stock.sku);

Imagine le besoin : « répercute les quantités de la table d'arrivages stock_in dans le qty des lignes stock correspondantes. » (Exécute correctement et l'explication apparaît.)

① Exécute d'abord SELECT sku, qty FROM stock ORDER BY sku; pour voir l'état avant toute mise à jour.

② Écris la version COALESCE : une mise à jour par sous-requête corrélée qui ajoute la somme de stock_in.add_qty à stock.qty. Pour les sku sans correspondance dans stock_in, traite NULL comme 0 pour que leur valeur reste inchangée. Relance un SELECT ensuite pour confirmer.

③ Écris ensuite la version EXISTS : une mise à jour qui utilise WHERE EXISTS (...) pour restreindre la cible aux « lignes qui ont un arrivage ». Relance SELECT et remarque que A001 / A004 reçoivent une deuxième fois le même arrivage — ils sont comptés en double.

UPDATE … FROM : joindre une autre table et mettre à jour

Écrire UPDATE table SET col = valeur FROM autre_table WHERE condition_jointure te permet de joindre la cible UPDATE avec la table nommée dans FROM et de mettre à jour d'un seul coup, en référençant directement les colonnes du côté joint dans SET.

-- Exemple UPDATE … FROM : augmenter le prix de 10 uniquement pour les articles ayant un arrivage
UPDATE stock
SET price = price + 10
FROM stock_in
WHERE stock.sku = stock_in.sku;

-- Seuls les sku joints (A001 / A004) voient leur prix monter
SELECT sku, price FROM stock ORDER BY sku;

Les lignes de stock sans correspondance côté FROM (stock_in) échouent à la condition de jointure dans WHERE et sortent automatiquement de la cible de mise à jour.

Contrairement à la sous-requête corrélée, pas besoin de se prémunir séparément contre NULL.

Imagine le besoin : « faire la même répercussion de stock que dans l'exercice 1, mais l'écrire plus compactement avec UPDATE … FROM. »

① Mets stock comme cible de la mise à jour et stock_in côté FROM, joins sur sku, et écris un UPDATE … FROM qui ajoute add_qty à qty.

② Termine le script avec SELECT sku, qty FROM stock ORDER BY sku; et confirme que seuls les sku joints (A001 / A004) voient leur stock augmenter.

DELETE avec condition par sous-requête, et contrôle du nombre de lignes touchées

Tu peux placer une sous-requête dans le WHERE d'un DELETE, comme DELETE FROM table WHERE col IN (SELECT ...) ou WHERE EXISTS (SELECT ...).

Ça permet des suppressions inexprimables avec une valeur fixe : « supprimer seulement les lignes présentes / absentes d'une autre table », ou « supprimer les lignes dont le résultat d'agrégat satisfait une condition ».

La suppression est destructive et irréversible, donc la pratique standard en production est de lancer d'abord le même WHERE dans un SELECT COUNT(*) pour vérifier le nombre de lignes touchées, puis de réécrire en DELETE.

Après exécution, vérifie avec SELECT COUNT(*) FROM table; pour confirmer que le nombre de lignes restantes correspond à ce que tu attendais.

-- D'abord vérifier ce qui serait supprimé : articles sous le prix moyen (stock n'est pas modifié)
SELECT sku, name, price FROM stock
WHERE price < (SELECT AVG(price) FROM stock);

-- Une fois satisfait, bascule en DELETE et vérifie le nombre restant
DELETE FROM stock
WHERE price < (SELECT AVG(price) FROM stock);
SELECT COUNT(*) AS remaining FROM stock;

Imagine le besoin : « supprimer de stock les candidats à l'abandon — les sku dont qty vaut 0 et qui n'ont aucun arrivage prévu dans stock_in. » Comme c'est le dernier exercice de l'article, tu vas effectuer une suppression destructive contre la table en place.

① Écris un DELETE avec condition par sous-requête qui retire les lignes de stock où qty vaut 0 et où sku n'existe pas dans stock_in.

② Après la suppression, exécute SELECT COUNT(*) AS remaining FROM stock; pour confirmer combien de lignes restent. Tu peux aussi utiliser SELECT * FROM stock ORDER BY sku; pour vérifier que A005 (Glue / qty 0 / pas d'arrivage) a disparu.