Jointures et sous-requêtes : modification de données

• Sous-requêtes
• Sommaire
• Union de plusieurs requêtes

Voici un petit chapitre bonus sur les jointures et les sous-requêtes. Vous allez apprendre ici à utiliser ces outils, non pas dans le cadre de la sélection de données comme on l'a fait jusqu'à présent, mais pour :

• l'insertion : les sous-requêtes vont permettre d'insérer des données dans une table à partir de données venant d'autres tables (mais pas exclusivement) ;
• la modification : jointures et sous-requêtes vont permettre non seulement d'utiliser des critères de sélection complexes mais également d'aller chercher les nouvelles valeurs dans d'autres tables ;
• la suppression de données : comme pour la modification, les critères de sélection pourront être plus complexes grâce aux jointures et sous-requêtes.

• Insertion
• Modification
• Suppression

Insertion

Pour l'insertion nous n'allons nous servir que de sous-requêtes, pas de jointures. Quoique… La sous-requête pourrait très bien contenir une jointure !

Sous-requête pour l'insertion

Vous venez d'acquérir un magnifique Maine Coon chez un éleveur voisin. Vous vous apprêtez à l'insérer dans votre base de données, mais vous ne vous souvenez absolument pas de l'id de la race Maine Coon, ni de celui de l'espèce Chat. Du coup, deux possibilités s'offrent à vous.

• Vous pouvez faire une requête pour sélectionner l'id de la race et de l'espèce, puis faire ensuite une requête d'insertion avec les données que vous venez d'afficher.
• Vous pouvez utiliser une sous-requête pour insérer directement l'id de la race et de l'espèce à partir du nom de la race.

Je ne sais pas ce que vous en pensez, mais moi je trouve la seconde option bien plus sexy !

Donc, allons-y. Qu'avons-nous comme information ?

• Nom : Yoda
• Date de naissance : 2010-11-09
• Sexe : mâle
• Espèce : chat
• Race : Maine coon

De quoi avons-nous besoin en plus pour l'insérer dans notre table ? L'id de l'espèce et de la race. Comment récupérer ces deux id ? Ça, vous savez faire : une simple requête suffit.

1

SELECT id AS race_id, espece_id FROM Race WHERE nom = 'Maine coon';

Bien, mais le but était de tout faire en une seule requête, pas d'insérer nous-mêmes 5 et 2 après les avoir récupérés.

C'est ici que les sous-requêtes interviennent. Nous allons utiliser une nouvelle syntaxe d'insertion.

INSERT INTO… SELECT

Cette syntaxe permet de sélectionner des éléments dans des tables, afin de les insérer directement dans une autre.

1
2
3
4
5

INSERT INTO nom_table
   [(colonne1, colonne2, ...)]
SELECT [colonne1, colonne2, ...]
FROM nom_table2
[WHERE ...]

Vous n'êtes bien sûr pas obligés de préciser dans quelles colonnes se fait l'insertion, si vous sélectionnez une valeur pour toutes les colonnes de la table. Ce sera cependant rarement le cas puisque nous avons des clés primaires auto-incrémentées.

Avec cette requête, il est absolument indispensable (sinon l'insertion ne se fera pas), d'avoir le même nombre de colonnes dans l'insertion et dans la sélection, et qu'elles soient dans le même ordre. Si vous n'avez pas le même nombre de colonnes, cela déclenchera une erreur. De plus, si l'ordre n'est pas bon, vous aurez probablement une insertion erronée.

Nous allons donc insérer le résultat de notre requête qui sélectionne les id de l'espèce et la race directement dans notre table Animal. Pas de souci, mais il faut également insérer le nom, le sexe, etc.

En effet ! Mais c'est très facile. Souvenez-vous, vous pouvez très bien faire des requêtes de ce type :

1

SELECT 'Yoda' AS nom;

Et donc, en combinant avec notre requête précédente :

1

SELECT id AS race_id, espece_id FROM Race WHERE nom = 'Maine coon';

Vous pouvez obtenir très facilement, et en une seule requête, tous les renseignements indispensables à l'insertion de notre petit Yoda !

1
2

SELECT 'Yoda', 'M', '2010-11-09', id AS race_id, espece_id
FROM Race WHERE nom = 'Maine coon';

Si tout se passe bien, cette requête devrait vous donner ceci :

Les noms qui sont donnés aux colonnes n'ont pas d'importance, mais vous pouvez les changer avec des alias si cela vous perturbe.

Venons-en maintenant à notre super insertion !

1
2
3
4

INSERT INTO Animal 
    (nom, sexe, date_naissance, race_id, espece_id)              -- Je précise les colonnes puisque je ne donne pas une valeur pour toutes.
SELECT  'Yoda', 'M', '2010-11-09', id AS race_id, espece_id     -- Attention à l'ordre !
FROM Race WHERE nom = 'Maine coon';

Sélectionnons maintenant les Maine coon de notre base, pour vérifier que l'insertion s'est faite correctement.

1
2
3
4
5

SELECT Animal.id, Animal.sexe, Animal.nom, Race.nom AS race, Espece.nom_courant as espece
FROM Animal
INNER JOIN Race ON Animal.race_id = Race.id
INNER JOIN Espece ON Race.espece_id = Espece.id
WHERE Race.nom = 'Maine coon';

Et qui voyons-nous apparaître dans les résultats ? Notre petit Yoda !

Modification

Utilisation des sous-requêtes

Pour la sélection

Imaginez que pour une raison bizarre, vous vouliez que tous les perroquets aient en commentaire "Coco veut un gâteau !".

Si vous saviez que l'id de l'espèce est 4, ce serait facile :

1

UPDATE Animal SET commentaires = 'Coco veut un gâteau' WHERE espece_id = 4;

Seulement voilà, vous ne savez évidemment pas que l'id de cette espèce est 4. Sinon, ce n'est pas drôle ! Vous allez donc utiliser une magnifique sous-requête pour modifier ces bruyants volatiles !

1
2

UPDATE Animal SET commentaires = 'Coco veut un gâteau !' WHERE espece_id = 
    (SELECT id FROM Espece WHERE nom_courant LIKE 'Perroquet%');

Bien sûr, toutes les possibilités de conditions que l'on a vues pour la sélection sont encore valables pour les modifications. Après tout, une clause WHERE est une clause WHERE !

Pour l'élément à modifier

Ce matin, un client demande à voir vos chats Bleu Russe, car il compte en offrir un à sa fille. Ni une ni deux, vous vérifiez dans la base de données, puis allez chercher Schtroumpfette, Filou, Caribou, Raccou, Callune, Feta et Cawette. Et là, horreur et damnation ! À l'instant où ses yeux se posent sur Cawette, le client devient vert de rage. Il prend à peine le temps de vous expliquer, outré, que Cawette n'est pas un Bleu Russe mais bien un Nebelung, à cause de ses poils longs, puis s'en va chercher un éleveur plus compétent.

Bon… L'erreur est humaine, mais autant la réparer rapidement. Vous insérez donc une nouvelle race dans la table ad hoc.

1
2

INSERT INTO Race (nom, espece_id, description)
VALUES ('Nebelung', 2, 'Chat bleu russe, mais avec des poils longs...');

Une fois cela fait, il vous reste encore à modifier la race de Cawette. Pour cela, vous avez besoin de l'id de la race Nebelung que vous venez d'ajouter. Vous vous en doutez, il est tout à fait possible de le faire grâce à une sous-requête :

1
2
3

UPDATE Animal SET race_id = 
    (SELECT id FROM Race WHERE nom = 'Nebelung' AND espece_id = 2)
WHERE nom = 'Cawette';

Il est bien entendu indispensable que le résultat de la sous-requête soit une valeur !

Limitation des sous-requêtes dans un UPDATE

Une limitation importante des sous-requêtes est qu'on ne peut pas modifier un élément d'une table que l'on utilise dans une sous-requête.

Exemple : vous trouvez que Callune ressemble quand même fichtrement à Cawette, et ses poils sont aussi longs. Du coup, vous vous dites que vous auriez dû également modifier la race de Callune. Vous essayez donc la requête suivante :

1
2
3

UPDATE Animal SET race_id = 
    (SELECT race_id FROM Animal WHERE nom = 'Cawette' AND espece_id = 2)
WHERE nom = 'Callune';

Malheureusement :

1

ERROR 1093 (HY000): You can't specify target table 'Animal' for update in FROM clause

La sous-requête utilise la table Animal, or vous cherchez à modifier le contenu de celle-ci. C'est impossible !

Il vous faudra donc utiliser la même requête que pour Cawette, en changeant simplement le nom (je ne vous fais pas l'affront de vous l'écrire).

Modification avec jointure

Imaginons que vous vouliez que, pour les tortues et les perroquets, si un animal n'a pas de commentaire, on lui ajoute comme commentaire la description de l'espèce. Vous pourriez sélectionner les descriptions, les copier, retenir l'id de l'espèce, et ensuite faire un UPDATE pour les tortues et un autre pour les perroquets. Ou alors, vous pourriez simplement faire un UPDATE avec jointure !

Voici la syntaxe que vous devriez utiliser pour le faire avec une jointure :

1
2
3
4
5
6

UPDATE Animal                                                             -- Classique !
INNER JOIN Espece                                                         -- Jointure.
    ON Animal.espece_id = Espece.id                                        -- Condition de la jointure.
SET Animal.commentaires = Espece.description                               -- Ensuite, la modification voulue.
WHERE Animal.commentaires IS NULL                                         -- Seulement s'il n'y a pas encore de commentaire.
AND Espece.nom_courant IN ('Perroquet amazone', 'Tortue d''Hermann');      -- Et seulement pour les perroquets et les tortues.

• Vous pouvez bien sûr mettre ce que vous voulez comme modifications. Ici, j'ai utilisé la valeur de la colonne dans l'autre table, mais vous auriez pu mettre Animal.commentaires = 'Tralala', et la jointure n'aurait alors servi qu'à sélectionner les tortues et les perroquets grâce au nom courant de l'espèce.
• Toutes les jointures sont possibles. Vous n'êtes pas limités aux jointures internes, ni à deux tables jointes.

Suppression

Cette partie sera relativement courte, puisque l'utilisation des sous-requêtes et des jointures est assez ressemblante entre la suppression et la modification. Simplement, pour la suppression, les sous-requêtes et jointures ne peuvent servir qu'à sélectionner les lignes à supprimer.

Utilisation des sous-requêtes

On peut, tout simplement, utiliser une sous-requête dans la clause WHERE. Par exemple, imaginez que nous ayons deux animaux dont le nom est "Carabistouille", un chat et un perroquet. Vous désirez supprimer Carabistouille-le-chat, mais garder Carabistouille-le-perroquet. Vous ne pouvez donc pas utiliser la requête suivante, qui supprimera les deux :

1

DELETE FROM Animal WHERE nom = 'Carabistouille';

Mais il suffit d'une sous-requête dans la clause WHERE pour sélectionner l'espèce, et le tour est joué !

1
2
3

DELETE FROM Animal
WHERE nom = 'Carabistouille' AND espece_id = 
    (SELECT id FROM Espece WHERE nom_courant = 'Chat');

Limitations

Les limitations sur DELETE sont les mêmes que pour UPDATE : on ne peut pas supprimer des lignes d'une table qui est utilisée dans une sous-requête.

Suppression avec jointure

Pour les jointures, c'est le même principe. Si je reprends le même problème que ci-dessus, voici comment supprimer la ligne voulue avec une jointure :

1
2
3
4

DELETE Animal                                        -- Je précise de quelles tables les données doivent être supprimées
FROM Animal                                          -- Table principale
INNER JOIN Espece ON Animal.espece_id = Espece.id    -- Jointure     
WHERE Animal.nom = 'Carabistouille' AND Espece.nom_courant = 'Chat';

Vous remarquez une petite différence avec la syntaxe "classique" de DELETE (sans jointure) : je précise le nom de la table dans laquelle les lignes doivent être supprimées juste après le DELETE. En effet, comme on utilise plusieurs tables, cette précision est obligatoire. Ici, on ne supprimera que les lignes d'Animal correspondantes.

En résumé

• INSERT INTO ... SELECT ... permet d'insérer dans une table des données provenant directement d'une autre (ou un mélange de données provenant d'une table, et de constantes définies par l'utilisateur).
• On peut utiliser des jointures et des sous-requêtes pour créer des critères de sélection complexes pour une modification ou une suppression de données.
• Dans le cas d'une suppression avec jointure, il est indispensable de préciser de quelle(s) table(s) les données doivent être supprimées
• On peut utiliser une sous-requête dans la clause SET d'une modification de données, pour définir la nouvelle valeur d'un champ à partir d'une autre table.

• Sous-requêtes
• Sommaire
• Union de plusieurs requêtes