Comment utiliser les sous-requêtes dans les instructions INSERT, UPDATE et DELETE ?

Saviez-vous que les sous-requêtes peuvent également être utilisées dans les instructions UPDATE, INSERT et DELETE ? Les sous-requêtes en SQL sont comme des blocs de construction qui peuvent être utilisés dans une variété d'instructions, pas seulement SELECT. Si vous trouvez les sous-requêtes utiles dans SELECT, lisez cet article pour découvrir à quel point elles peuvent être utiles dans d'autres instructions. Vous serez en mesure de créer des requêtes SQL beaucoup plus complexes et puissantes en un rien de temps !

Si vous souhaitez rafraîchir vos connaissances sur les sous-requêtes, je vous recommande le cours interactif Exercices Pratiques de SQL cours interactif. Il contient plus de 80 exercices sur les sous-requêtes et d'autres constructions difficiles sur SELECT.

Point de départ : Un bref rappel des sous-requêtes

Commençons par un rappel rapide de ce qu'est une sous-requête. Les sous-requêtes étant le plus souvent utilisées dans les instructions SELECT, nous allons examiner un exemple de sous-requête simple dans une instruction SELECT.

Nous pouvons définir une sous-requête comme une requête à l'intérieur d'une autre requête. Si les sous-requêtes sont le plus souvent utilisées dans la clause WHERE des instructions SELECT, elles peuvent être utilisées dans plusieurs autres clauses, notamment WHERE, FROM et HAVING.

Voyons la base de données que nous allons utiliser à titre d'exemple. Imaginez que vous êtes le propriétaire d'un magasin de vin, et que vous avez une base de données simple avec 3 tables pour gérer le fonctionnement du magasin.

La première table est wineElle contient les produits que vous vendez, avec le nom, le prix, le nombre de bouteilles en stock, etc. pour chaque vin. La seconde est orderqui stocke les commandes que nous recevons de nos clients, y compris le nom du vin commandé et la quantité commandée, entre autres informations.

wine

order

Supposons que nous voulions obtenir la liste des vins pour lesquels nous n'avons jamais reçu de commande. La requête ressemblera à ceci :

SELECT  name,
FROM    wine
WHERE   name NOT IN ( SELECT wine_name FROM order )

La sous-requête renvoie les noms de tous les vins pour lesquels nous avons reçu des commandes. Ensuite, la requête externe, en utilisant l'opérateur NOT IN, obtient les noms des vins qui n'ont jamais été inclus dans une commande. Le SQL est aussi simple qu'il est puissant ! Si vous souhaitez revoir certains concepts de sous-requêtes, je vous suggère le cours SQL pour les débutants où vous trouverez une section complète sur les sous-requêtes.

Première étape : Utilisation de sous-requêtes dans les instructions INSERT

Utilisons maintenant une sous-requête dans une déclaration INSERT. L'idée est d'insérer l'ensemble des résultats d'une sous-requête ou d'une déclaration SELECT dans une table. À titre d'exemple, imaginons que nous voulions créer des factures pour toutes les commandes de vin que nous avons reçues au cours de la journée. Voici une vue partielle de notre invoice table :

Supposons que la date d'aujourd'hui soit le 28 juin 2020, et que nous voulions insérer les enregistrements des factures associées aux commandes d'aujourd'hui. Nous pouvons utiliser la requête suivante SELECT pour générer les données des factures :

SELECT  o.date, 
        o.client_id, 
        o.quantity * w.price as amount, 
        o.quantity || ’ bottles of ‘ || o.wine_name,
        o.order_id
FROM    order o 
JOIN    wine w ON w.name = o.wine_name
WHERE   o.date = ‘2020-06-28’

Le résultat de cette requête est ci-dessous :

Voici exactement ce que nous voulons insérer dans la table invoice.

Si nous ajoutons simplement une clause INSERT avant la requête, nous pouvons insérer le résultat de la requête dans le tableau. winecomme nous pouvons le voir dans l'exemple suivant :

INSERT INTO invoice (date, client_id, amount, wine_name, order_id)
SELECT  o.date, 
        o.client_id, 
        o.quantity * w.price as amount, 
        o.quantity || ’ bottles of ‘ || o.wine_name,
        o.order_id
FROM    order o 
JOIN    wine w ON w.name = o.wine_name
WHERE   o.date = ‘2020-06-28’

Notez ce qui suit :

• Nous devons nommer explicitement les colonnes de la table invoice dans laquelle nous effectuons des insertions.
• Les colonnes de la liste SELECT doivent être dans le même ordre que les colonnes de la table.
• Nous avons omis la colonne invoice_id pour permettre à la base de données de choisir la valeur suivante à l'aide d'un générateur de séquence par défaut.

Après avoir exécuté la clause INSERT, la table invoice contiendra les nouveaux enregistrements de factures pour les commandes du jour. Nous pouvons le voir ci-dessous, avec les nouveaux enregistrements en rouge :

Supposons qu'un jour donné, nous ayons accidentellement exécuté deux fois le programme INSERT et que nos clients aient ainsi reçu deux factures pour chaque commande. Nous ne voulons pas répéter la même erreur à l'avenir ! Pour éviter cela, nous ajoutons une sous-requête à l'instruction INSERT pour vérifier si une facture portant le même nom order_id existe déjà. Voici la nouvelle version de la requête INSERT. La sous-requête ajoutée à la fin identifie les factures qui existent déjà, et la requête externe les élimine en utilisant l'opérateur NOT IN.

INSERT INTO invoice (date, client_id, amount, wine_name, order_id)
SELECT order.date, 
       order.client_id, 
       o.quantity * w.price as amount, 
       o.quantity || ’ bottles of ‘ || o.wine_name,
       o.order_id
FROM   order o 
JOIN   wine ON wine.name = order.wine_name
WHERE  order.date = ‘2020-06-28’
  AND  o.order_id NOT IN (SELECT order_id 
                           FROM invoice i 
                           WHERE i.order_id=o.order_id )

Si vous voulez améliorer vos compétences dans l'utilisation des instructions INSERT, UPDATE, et DELETE, je vous suggère le cours Comment modifier une base de données avec SQL où vous pourrez voir plusieurs exemples de ces instructions.

Deuxième étape : sous-requêtes dans les instructions UPDATE

Comme SELECT, l'instruction UPDATE peut contenir une sous-requête à plusieurs endroits ou clauses. Dans une déclaration UPDATE, les deux clauses dans lesquelles les sous-requêtes sont le plus souvent utilisées sont SET et WHERE.

La clause SET permet de définir la nouvelle valeur de la colonne modifiée par l'instruction UPDATE. Nous pouvons utiliser une sous-requête pour obtenir cette nouvelle valeur, qui peut être sélectionnée dans n'importe quelle table ou n'importe quelle sous-requête valide, à condition de ne renvoyer qu'un seul enregistrement avec une seule colonne pour chaque enregistrement mis à jour. Le type de données de la colonne renvoyée par la sous-requête doit être du même type que celui de la colonne en cours de modification.

Créons un UPDATE pour maintenir à jour notre stock de bouteilles de vin. Nous exécuterons un UPDATE à la fin de la journée pour refléter ce que nous avons vendu aujourd'hui. Le code ressemblera à ceci :

UPDATE wine w
SET stock = stock - (
                     SELECT SUM (quantity)
                     FROM order
                     WHERE date = CURRENT_DATE AND order.wine_name = w.name
                    )

Si nous n'utilisons pas la clause WHERE dans la requête UPDATE, nous finirons par modifier tous les enregistrements de la table wine, y compris les enregistrements des vins que nous n'avons pas vendus aujourd'hui. La sous-requête renvoie un NULL pour tout vin qui n'a pas été vendu aujourd'hui, et nous aurions par erreur SET la colonne stock à NULL, puisque le résultat de l'expression "stock - NULL" est NULL. Nous devons corriger ce problème.

Il existe deux approches pour le corriger. La première consiste à modifier l'expression SUM(quantity) pour qu'elle renvoie un zéro au lieu de NULL. Pour cela, nous devons simplement utiliser la fonction COALESCE, comme ceci :

UPDATE wine w
SET stock = stock - (
                     SELECT coalesce(SUM (quantity), 0)
                     FROM order
                     WHERE date = CURRENT_DATE AND order.wine_name = w.name
                    )

La deuxième approche consiste à ajouter une sous-requête dans la clause WHERE pour modifier uniquement les vins qui ont été commandés aujourd'hui et maintenir l'expression SUM(quantity) telle quelle. La requête suivante illustre cette approche :

UPDATE wine w
SET stock = stock - (
                     SELECT SUM (quantity)
                     FROM order
                     WHERE date = CURRENT_DATE AND order.wine_name = w.name
                    )
WHERE w.name IN (SELECT order.wine_name FROM order WHERE date = CURRENT_DATE)

Ce UPDATE n'est pas idéal : cette clause SET utilise une sous-requête corrélée. Une sous-requête corrélée est une requête que la base de données doit exécuter plusieurs fois - une fois pour chaque ligne modifiée dans la table. Dans notre exemple, la requête obtient l'adresse SUM(quantity) pour chaque vin vendu aujourd'hui. Bien que les sous-requêtes corrélées puissent être puissantes, il est préférable de les éviter autant que possible pour des raisons de bonnes pratiques. Ici, nous pouvons éviter la sous-requête corrélée en utilisant une clause FROM dans la requête UPDATE, comme nous pouvons le voir ci-dessous :

UPDATE wine w
SET stock = stock - subquery.total_in_orders
FROM ( SELECT wine_name, sum( order_quantity ) AS total_in_orders  
       FROM   order 
       WHERE  date = TODAY 
       GROUP BY wine_name 
     ) subquery
WHERE w.name = subquery.wine_name 

Comme ressource complémentaire, je vous suggère l'article Beginners Guide to the SQL Subquery, où vous pourrez apprendre l'ABC des sous-requêtes à travers des exemples de complexité variable.

Troisième étape : Sous-requêtes dans les instructions DELETE

Avec l'instruction DELETE, les sous-requêtes peuvent être utilisées uniquement dans une clause WHERE. Disons que nous voulons éliminer les enregistrements des vins pour lesquels nous n'avons reçu aucune commande au cours des 6 derniers mois. Nous pouvons créer une sous-requête qui renvoie les vins vendus au cours des 6 derniers mois, puis identifier les enregistrements que nous voulons supprimer dans la table wine en utilisant l'opérateur NOT IN. Voyons comment SQL procède :

DELETE FROM wine w
WHERE name NOT IN ( SELECT wine_name 
                    FROM   order 
                    WHERE  date >= CURRENT_DATE - interval ‘6 Months’ 
                  )

Supposons maintenant que nous voulions éliminer les vins pour lesquels le total des commandes au cours des 6 derniers mois était inférieur à 10 unités. L'instruction DELETE ressemblera à ceci :

DELETE FROM wine w
WHERE 10 > ( SELECT SUM(quantity) 
                    FROM   order o 
                    WHERE  o.wine_name = w.name 
                      AND  date >= CURRENT_DATE - interval ‘6 Months’
                  )

Ici, la sous-requête renvoie la quantité de bouteilles commandées au cours des 6 derniers mois pour chaque vin. En comparant cette quantité à 10, nous pouvons déterminer si un vin donné doit être éliminé.

Arrivée à la fin de la tournée des sous-requêtes

Les sous-requêtes sont comme des blocs de construction en SQL. Nous avons vu comment elles peuvent être utilisées à différents endroits, par exemple dans les instructions SELECT ou dans les instructions de modification telles que INSERT, UPDATE et DELETE. Nous avons vu comment nous pouvions utiliser une sous-requête dans différentes clauses et qu'il existait différentes façons d'utiliser les sous-requêtes dans les instructions INSERT, UPDATE et DELETE. Si vous souhaitez en savoir plus sur les sous-requêtes, je vous suggère l'article Subquery vs. CTE : A SQL Primer, dans lequel un type de sous-requête appelé CTE est expliqué avec de nombreux exemples et détails. Enfin, il existe deux cours contenant de nombreuses informations utiles sur les sous-requêtes, avec de nombreux exemples : SQL pour les débutants où vous trouverez une section complète sur les sous-requêtes, et Comment modifier une base de données avec SQL qui est un cours plus avancé. Allons améliorer vos compétences en SQL !