Le but de ce billet est de réaliser des opérations de mise à jour ou de suppression à l’aide d’une jointure, externe ou interne.
Voici le schéma qui nous sert de base de travail :

• une table client
• une table produit
• une table commande, avec deux contraintes de clé étrangère partant vers chacune des tables

Le SQL du schéma

create table client(
id tinyint unsigned primary key auto_increment,
nom varchar(30) not null,
prenom varchar(20) not null);

insert into client (nom, prenom) values 
('Ferrandez', 'Sébastien'), ('Jambon', 'Paul');

create table produit(
id tinyint unsigned primary key auto_increment,
designation varchar(30) not null,
prix float (6,2) unsigned not null);

insert into produit (designation, prix) values 
('Mousse à raser', 7.99), ('Stylo BIC', 4.99);

create table commande (
id tinyint unsigned primary key auto_increment, 
id_client tinyint unsigned , 
id_produit tinyint unsigned, 
quantite tinyint unsigned default 1,
date timestamp);

alter table commande add constraint foreign key (id_client) 
references client(id);

alter table commande add constraint foreign key (id_produit)
references produit(id);

insert into commande(id_client,id_produit, quantite )
values (1,1,2), (1,2,1), (2,2,1), (2,1,2);

Les jointures, externes et internes

Le principe de la jointure est trivial, on crée comme une sorte de « pont » entre au moins 2 tables selon une condition qu’on va très simplement appeler « condition de jointure ». Une condition de jointure n’est pas forcément une clause d’égalité !

Prenons l’exemple de deux tables sans rapport quelconque :

create table eleve (nom varchar(20), age tinyint unsigned);
insert into eleve values ('Ferrandez', 10), ('Lucas', 30);
create table produit (nom varchar(20), prix float(6,2) unsigned);
insert into produit values ('iPhone5', 499), ('Jambon', 10);

Qu’est-ce qui nous empêche de faire une jointure entre un élève et un produit basé sur l’égalité de valeur de l’âge de l’élève et du prix du produit ?

SELECT * 
FROM  eleve 
INNER JOIN produit ON ( eleve.age = produit.prix ) 

Nous obtenons

+-----------+------+--------+-------+
| nom       | age  | nom    | prix  |
+-----------+------+--------+-------+
| Ferrandez |   10 | Jambon | 10.00 |
+-----------+------+--------+-------+
1 row in set (0.00 sec)

Une jointure ne se fait pas uniquement sur la base d’une égalité de valeur, on peut utiliser d’autres opérateurs arithmétiques :

SELECT * 
FROM  eleve
INNER JOIN produit ON ( eleve.age <= produit.prix ) 

Celle-ci remontera les tuples suivants :

+-----------+------+---------+--------+
| nom       | age  | nom     | prix   |
+-----------+------+---------+--------+
| Ferrandez |   10 | iPhone5 | 499.00 |
| Lucas     |   30 | iPhone5 | 499.00 |
| Ferrandez |   10 | Jambon  |  10.00 |
+-----------+------+---------+--------+
3 rows in set (0.00 sec)   

Et pire, qu’est-ce qui nous empêche de faire une jointure sur des champs qui n’ont pas le même type de données (horreur !) :

SELECT * 
FROM  eleve
INNER JOIN produit ON ( eleve.age <= produit.nom ) 

Si on a le droit de faire ça, c’est que l’optimiseur MySQL effectue des conversions de type pour effectuer des comparaisons (différence, égalité etc.). Mais recentrons-nous sur le sujet du billet ! Une requête de jointure externe, contrairement aux internes, retourne à la fois les correspondances (c’est à dire, les enregistrements satisfaisant la clause de jointure) et les non-correspondances (celles qui ne la satisfont pas). Voyons ce que donne :

SELECT * 
FROM  eleve 
LEFT JOIN produit ON ( eleve.age = produit.prix ) 

+-----------+------+--------+-------+
| nom       | age  | nom    | prix  |
+-----------+------+--------+-------+
| Ferrandez |   10 | Jambon | 10.00 |
| Lucas     |   30 | NULL   |  NULL |
+-----------+------+--------+-------+
2 rows in set (0.00 sec)

Les non-correspondances sont signalées par la présence du marqueur NULL. Si vous choisissez de les remonter, rien de plus facile :

SELECT eleve.*
FROM  eleve 
LEFT JOIN produit ON ( eleve.age = produit.prix ) 
WHERE produit.prix IS NULL

Notez bien que le même test, mais sur produit.nom IS NULL aurait également fonctionné !
Bref, nous savons maintenant ce qu’est une jointure interne (que ce qui correspond) et une jointure externe (ce qui correspond + ce qui ne correspond pas).

La mise à jour

Nous allons mettre à jour les commandes du client Sébastien Ferrandez en disant qu’elles ont eu lieu le 24 Décembre 2013 à 23:59:59 :

UPDATE commande INNER JOIN client
ON (commande.id_client = client.id)
SET commande.date = '2013-12-24 23:59:59'
WHERE client. nom = 'ferrandez'
AND client.prenom = 'sebastien'

Notez que nous sommes en collation latin1_general_ci et que nous ne tenons donc compte ni des accents ni des majuscules…
Si d’aventure vous souhaitez modifier d’autres champs (y compris dans la table client, pas que commande !), séparez les dans SET avec des virgules !

Vous pouvez très bien utiliser la jointure externe pour mettre a jour des enregistrements ne se trouvant pas dans la table commande (nous aurions par exemple dans la table client un champ booléen A_DEJA_COMMANDE que nous mettrions à 0 si aucune commande n’était trouvée pour celui-ci).

La suppression

Insérons un nouveau tuple dans client :

insert into client (nom, prenom) values ('Diego', 'Miguel');

Et effectuons sa suppression immédiate en disant « J’efface les clients qui n’ont pas passé de commande »

DELETE client.* FROM client
LEFT OUTER JOIN commande 
ON ( client.id = commande.id_client )
WHERE commande.id_client IS NULL

N’oubliez pas que vous n’êtes absolument pas limités à UNE jointure et que vous pouvez très bien alterner jointures externes et internes dans la même requête ! Entraînez-vous sur des petits schémas tout simples du même genre que celui que nous avons vu dans ce billet et tout ça n’aura bientôt plus de secret pour vous !