INSERT, UPDATE et DELETE (LMD)

INSERT, UPDATE et DELETE sont les trois instructions SQL qui modifient les lignes d'une table : INSERT en ajoute, UPDATE en modifie, DELETE en supprime. Elles forment le LMD, le langage de manipulation des données. Les exemples modifient la base d'exemple parc : rejoue son script avant chaque série d'exercices pour retrouver les résultats du cours.

INSERT

Emma Chevalier, alternante, rejoint le service informatique aujourd'hui :

INSERT INTO employe (nom, prenom, email, date_embauche, id_service, id_responsable)
VALUES ('Chevalier', 'Emma', 'emma.chevalier@sorbier.example', '2026-10-28', 5, 10);

Les colonnes absentes de la liste prennent leur valeur par défaut : id_employe reçoit le numéro suivant de l'auto-incrément (15) et telephone reste à NULL. Pour connaître l'identifiant attribué, juste après l'INSERT, dans la même connexion :

SELECT LAST_INSERT_ID();

Plusieurs lignes en une seule instruction, c'est plus rapide qu'une instruction par ligne :

INSERT INTO logiciel (nom, editeur, categorie, nb_licences) VALUES
  ('VLC',  'VideoLAN',      'multimédia', NULL),
  ('GIMP', 'The GIMP Team', 'multimédia', NULL);

Après un INSERT de plusieurs lignes, LAST_INSERT_ID() renvoie l'identifiant de la première ligne insérée (ici 9).

Quelques règles d'écriture :

• les chaînes et les dates s'écrivent entre apostrophes simples : '2026-10-28' ;
• une apostrophe dans une chaîne se double : 'Lenteurs réseau à l''entrepôt' ;
• le mot-clé DEFAULT demande explicitement la valeur par défaut : VALUES (..., DEFAULT, ...) ;
• INSERT INTO service VALUES (7, 'RH', 'Ressources humaines', 3);, sans liste de colonnes, fonctionne mais casse dès qu'on ajoute une colonne à la table : nomme toujours les colonnes.

Un ticket ouvert sans préciser la date, la priorité ni le statut reçoit les valeurs par défaut de la table : maintenant, 'normale' et 'ouvert'.

INSERT INTO ticket (titre, id_demandeur) VALUES ('Souris défectueuse', 9);

INSERT … SELECT

INSERT … SELECT insère le résultat d'une requête. Premier usage : archiver. CREATE TABLE … LIKE copie la structure d'une table, colonnes et index (mais pas les clés étrangères) :

CREATE TABLE ticket_archive LIKE ticket;

INSERT INTO ticket_archive
SELECT * FROM ticket
WHERE statut = 'resolu' AND date_cloture < '2026-10-01';

Cinq tickets résolus avant le 1er octobre sont copiés (1 à 5). Deuxième usage : préparer le poste PC-0014, destiné à Paul Martin, comptable, avec les mêmes logiciels que le poste PC-0001 de sa collègue :

INSERT INTO installation (id_poste, id_logiciel, date_installation)
SELECT 14, id_logiciel, '2026-10-28'
FROM installation
WHERE id_poste = 1;

Le SELECT renvoie trois lignes (Firefox, Microsoft 365, Sage), qui deviennent trois installations du poste 14. Le nombre et l'ordre des colonnes du SELECT doivent correspondre à la liste de l'INSERT. Remarque au passage que rien n'a vérifié le nombre de licences Sage : la base en compte maintenant 3 utilisées sur 3. Ce contrôle demande une transaction ou un trigger.

UPDATE

-- Une valeur
UPDATE employe SET telephone = '01 99 00 30 03' WHERE id_employe = 6;

-- Une valeur calculée à partir de l'ancienne
UPDATE logiciel SET nb_licences = nb_licences + 2 WHERE nom = 'Odoo';

-- Plusieurs colonnes à la fois
UPDATE ticket
SET statut = 'resolu', date_cloture = '2026-10-28 10:00:00'
WHERE id_ticket = 14;

Le WHERE choisit les lignes. L'expression à droite du = peut utiliser l'ancienne valeur de la colonne : nb_licences + 2 lit la valeur avant modification. Attention à un piège propre à MySQL quand un même SET modifie plusieurs colonnes : il évalue les affectations de gauche à droite, et chacune voit les colonnes déjà modifiées. Dans SET a = a + 1, b = a, b reçoit la nouvelle valeur de a ; PostgreSQL, SQL Server et la norme lui donnent l'ancienne.

Pour modifier une table en fonction d'une autre, la forme portable passe par une sous-requête. Les tickets en priorité basse, non résolus, ouverts par la logistique (service 4) passent en priorité normale :

UPDATE ticket
SET priorite = 'normale'
WHERE priorite = 'basse'
  AND statut <> 'resolu'
  AND id_demandeur IN (SELECT id_employe FROM employe WHERE id_service = 4);

Deux lignes changent : les tickets 6 et 12. MySQL accepte aussi une jointure dans l'UPDATE :

UPDATE ticket t
JOIN employe e ON e.id_employe = t.id_demandeur
SET t.priorite = 'normale'
WHERE t.priorite = 'basse' AND t.statut <> 'resolu' AND e.id_service = 4;

DELETE

-- Retirer LibreOffice du poste en stock PC-0013
DELETE FROM installation WHERE id_poste = 13 AND id_logiciel = 2;

-- Retirer tous les logiciels des postes en stock
DELETE FROM installation
WHERE id_poste IN (SELECT id_poste FROM poste WHERE id_employe IS NULL);

Sans WHERE, DELETE vide toute la table, et UPDATE modifie toutes les lignes. Pour t'en protéger, le client MySQL propose le mode safe updates : il refuse tout UPDATE ou DELETE dont le WHERE n'utilise pas une colonne indexée et qui n'a pas de LIMIT. MySQL Workbench l'active par défaut ; dans le client en ligne de commande, lance mysql --safe-updates ou tape :

SET SQL_SAFE_UPDATES = 1;
UPDATE employe SET telephone = NULL;

ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.

MySQL refuse de modifier une table en lisant la même table dans une sous-requête. Supprimer les interventions plus courtes que la moyenne :

DELETE FROM intervention
WHERE duree_min < (SELECT AVG(duree_min) FROM intervention);

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

La parade : passer par une table dérivée, que MySQL calcule entièrement avant de supprimer quoi que ce soit.

DELETE FROM intervention
WHERE duree_min < (SELECT moyenne
                   FROM (SELECT AVG(duree_min) AS moyenne FROM intervention) AS m);

Insérer ou mettre à jour

Tu importes chaque mois l'état des licences fourni par les éditeurs. Si le logiciel existe (même nom, une colonne UNIQUE), tu mets à jour son nombre de licences ; sinon, tu l'ajoutes. C'est un upsert.

INSERT INTO logiciel (nom, editeur, categorie, nb_licences)
VALUES ('Odoo', 'Odoo SA', 'gestion', 10) AS nouveau
ON DUPLICATE KEY UPDATE nb_licences = nouveau.nb_licences;

MySQL tente l'insertion. Elle viole la contrainte UNIQUE sur nom : MySQL exécute alors la partie UPDATE sur la ligne existante. L'alias nouveau désigne la ligne qu'on voulait insérer (MySQL 8.0.19 et plus). Le client affiche 2 rows affected pour une mise à jour, 1 pour une insertion, 0 si rien n'a changé.

REPLACE INTO ressemble à un upsert, mais il supprime la ligne en conflit avant d'en insérer une nouvelle :

REPLACE INTO logiciel (nom, editeur, categorie, nb_licences)
VALUES ('Odoo', 'Odoo SA', 'gestion', 10);

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails
(`parc`.`installation`, CONSTRAINT `fk_installation_logiciel` FOREIGN KEY (`id_logiciel`) REFERENCES `logiciel` (`id_logiciel`))

Lire les erreurs de contraintes

Une erreur MySQL a un numéro, un code SQLSTATE normalisé et un message. Le numéro suffit à reconnaître la cause.

Les erreurs 1364, 1406 et 1366 viennent du mode strict, actif par défaut depuis MySQL 5.7 (STRICT_TRANS_TABLES dans la variable sql_mode). Sans lui, MySQL tronque ou remplace la valeur et se contente d'un avertissement : les données deviennent fausses sans que personne ne le voie.

Importer et exporter du CSV

Un fichier postes.csv exporté d'Excel au format « CSV UTF-8 », en français, utilise le point-virgule comme séparateur, la virgule décimale, des dates JJ/MM/AAAA et des fins de ligne Windows :

num_inventaire;type_poste;marque;modele;date_achat;prix_achat
PC-0016;portable;Dell;Latitude 5440;15/10/2026;1099,00

LOAD DATA l'importe en convertissant au passage les colonnes qui ne sont pas au format MySQL :

LOAD DATA LOCAL INFILE '/home/emma/postes.csv'
INTO TABLE poste
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(num_inventaire, type_poste, marque, modele, @date_achat, @prix)
SET date_achat = STR_TO_DATE(@date_achat, '%d/%m/%Y'),
    prix_achat = REPLACE(@prix, ',', '.');

• LOCAL lit le fichier sur ton poste et l'envoie au serveur. L'option local_infile doit être activée côté serveur (elle est désactivée par défaut depuis MySQL 8.0) et côté client (mysql --local-infile=1).
• Sans LOCAL, le fichier doit se trouver sur le serveur, dans le dossier autorisé par la variable secure_file_priv, et ton compte a besoin du privilège FILE.
• IGNORE 1 LINES saute la ligne d'en-tête. '\r\n' correspond aux fins de ligne de Windows : avec '\n' seul, la dernière colonne garderait un \r invisible.
• CHARACTER SET doit décrire le codage réel du fichier. Le format Excel « CSV (séparateur : point-virgule) » est codé en Windows-1252 : déclare alors CHARACTER SET latin1 (le latin1 de MySQL est en fait Windows-1252), sinon les accents arrivent abîmés. Le plus simple reste d'exporter en « CSV UTF-8 ».
• Les variables @date_achat et @prix reçoivent la valeur brute ; la clause SET la convertit.

L'export inverse s'écrit côté serveur :

SELECT id_ticket, titre, statut, date_ouverture
FROM ticket
INTO OUTFILE '/var/lib/mysql-files/tickets.csv'
  FIELDS TERMINATED BY ';' ENCLOSED BY '"'
  LINES TERMINATED BY '\n';

En pratique, tu passeras souvent par l'assistant d'import ou d'export de DBeaver, phpMyAdmin ou Workbench, qui pose les mêmes questions : séparateur, encodage, ligne d'en-tête, format des dates.

Exercices

INSERT INTO employe (nom, prenom, email, date_embauche, id_service, id_responsable)
VALUES ('Chevalier', 'Emma', 'emma.chevalier@sorbier.example', '2026-10-28', 5, 10);

UPDATE poste SET id_employe = 15 WHERE num_inventaire = 'PC-0013';

SELECT num_inventaire, marque, modele, id_employe FROM poste WHERE num_inventaire = 'PC-0013';

INSERT INTO intervention (id_ticket, id_technicien, date_intervention, duree_min, compte_rendu)
VALUES (6, 12, '2026-10-28 09:00:00', 25, 'Ventilateur remplacé');

UPDATE ticket
SET statut = 'resolu', date_cloture = '2026-10-28 09:30:00'
WHERE id_ticket = 6;

DELETE FROM installation
WHERE id_logiciel = 2
  AND id_poste IN (SELECT id_poste FROM poste WHERE id_employe IS NULL);

DELETE FROM ticket
WHERE statut = 'ouvert' AND date_ouverture < '2026-10-01';

INSERT INTO service (code, libelle) VALUES ('INFO', 'Informatique bis');
INSERT INTO poste (num_inventaire, type_poste, marque, modele, date_achat, prix_achat)
  VALUES ('PC-0016', 'tablette', 'Apple', 'iPad', '2026-10-01', 499);
INSERT INTO employe (nom, prenom, email, date_embauche, id_service)
  VALUES ('Roy', 'Ana', 'ana.roy@sorbier.example', '2026-11-02', 12);
DELETE FROM logiciel WHERE id_logiciel = 8;
UPDATE intervention SET duree_min = -5 WHERE id_intervention = 1;

INSERT INTO logiciel (nom, editeur, categorie, nb_licences) VALUES
  ('Sage 100 Comptabilité', 'Sage', 'gestion', 4),
  ('Sage Paie',             'Sage', 'gestion', 2) AS nouveau
ON DUPLICATE KEY UPDATE nb_licences = nouveau.nb_licences;