Manipuler les données en SQL : INSERT, UPDATE, DELETE et transactions

Un ticket arrive : « L'adresse IP du serveur web-prod-01 a changé, il faut la corriger. » Vous ouvrez sqlite3, vous tapez un UPDATE… et vous venez potentiellement de modifier toutes les lignes de la table si vous avez oublié le WHERE.

La manipulation des données (DML, Data Manipulation Language) est la partie de SQL qui modifie le contenu des tables : insertion, modification, suppression. Contrairement au SELECT qui ne lit que les données, ces opérations sont irréversibles une fois validées. C'est pourquoi les transactions existent : elles vous permettent de tester vos modifications et de les annuler si quelque chose ne va pas.

Ce guide vous apprend à écrire des INSERT, UPDATE et DELETE en toute sécurité, et à adopter les réflexes qui évitent les incidents en production.

Ce que vous allez apprendre

• Insérer des données avec INSERT INTO (une ou plusieurs lignes)
• Modifier des données existantes avec UPDATE … SET … WHERE
• Supprimer des lignes avec DELETE FROM … WHERE
• Protéger vos modifications avec les transactions (BEGIN, COMMIT, ROLLBACK)
• Récupérer les données modifiées avec RETURNING
• Éviter les erreurs classiques : UPDATE sans WHERE, DELETE sans WHERE

Dans quel contexte ?

Les opérations DML interviennent régulièrement dans le travail d'un admin ou DevOps :

• Corriger l'adresse IP d'un serveur après un changement réseau
• Insérer un nouveau serveur dans l'inventaire après un provisionnement
• Supprimer les alertes obsolètes acquittées depuis plus de 30 jours
• Écrire des scripts de migration de données dans un pipeline CI/CD
• Désactiver un compte utilisateur dans la base d'équipe

Ce guide ne couvre pas…

Le périmètre est volontairement resserré sur les trois ordres qui écrivent dans une table et sur le mécanisme qui les protège. Les sujets voisins ci-dessous ont leur propre guide, avec leurs propres pièges.

• La lecture des données (SELECT) → voir le guide Syntaxe de base
• Les jointures dans les requêtes d'écriture (UPDATE … FROM, DELETE … USING) → voir le guide Jointures et sous-requêtes
• La création ou modification de la structure des tables (CREATE TABLE, ALTER TABLE) → voir le guide SQL avancé

Prérequis

• Avoir suivi le guide Syntaxe de base (SELECT, WHERE)
• La base fil rouge infra.db chargée dans ~/Projets/cours-sql

Travaillez sur votre base de cours, jamais en production

Les exercices de ce guide modifient des données. Si vous avez cassé votre base : rm infra.db && sqlite3 infra.db < init-db.sql

INSERT : ajouter des données

INSERT INTO ajoute une ou plusieurs lignes dans une table. C'est le seul des trois ordres qui ne peut rien détruire : au pire, il échoue sur une contrainte. C'est aussi pour cette raison qu'on commence par lui.

Quatre formes couvrent l'essentiel des besoins : l'insertion unitaire, l'insertion en masse, la recopie depuis une autre table avec INSERT … SELECT, et l'upsert qui met à jour la ligne quand elle existe déjà.

INSERT INTO avec colonnes explicites

Listez toujours les colonnes explicitement, cela rend la requête lisible et résistante aux changements de schéma :

INSERT INTO servers (hostname, ip_address, os, os_version, environment, datacenter, cpu_cores, ram_gb, disk_gb)
VALUES ('cache-prod-01', '10.1.5.10', 'Debian', '12', 'production', 'paris-dc1', 2, 4, 50);

Les colonnes non listées prennent leur valeur par défaut (active = 1, created_at = date courante, notes = NULL).

Insérer plusieurs lignes en une seule requête

Plutôt que d'exécuter 3 INSERT séparés, insérez en masse :

INSERT INTO alerts (server_id, severity, message)
VALUES
  (1, 'info',    'Mise à jour apt disponible'),
  (2, 'info',    'Mise à jour apt disponible'),
  (5, 'warning', 'Certificat staging expire dans 15 jours');

C'est nettement plus performant, une seule transaction au lieu de trois allers-retours.

INSERT … SELECT : copier depuis une autre table

Vous pouvez insérer le résultat d'un SELECT. Par exemple, archiver les alertes acquittées de plus de 30 jours (en supposant qu'une table archived_alerts existe) :

INSERT INTO archived_alerts (server_id, severity, message, created_at)
SELECT server_id, severity, message, created_at
FROM   alerts
WHERE  acknowledged = 1 AND created_at < datetime('now', '-30 days');

Upsert : insérer ou mettre à jour

Quand vous ne savez pas si la ligne existe déjà, le mécanisme d'upsert évite les erreurs de doublons. SQLite utilise la même syntaxe que PostgreSQL (ON CONFLICT) :

INSERT INTO servers (hostname, ip_address, os, os_version, environment, datacenter, cpu_cores, ram_gb, disk_gb)
VALUES ('web-prod-01', '10.1.1.20', 'Debian', '12', 'production', 'paris-dc1', 4, 8, 100)
ON CONFLICT (hostname)
DO UPDATE SET ip_address = EXCLUDED.ip_address;

ON CONFLICT cible une contrainte unique (hostname). EXCLUDED référence les valeurs qu'on tentait d'insérer.

Syntaxe MySQL différente

MySQL utilise ON DUPLICATE KEY UPDATE au lieu de ON CONFLICT. La logique est la même, mais la syntaxe n'est pas interchangeable.

UPDATE : modifier des données

UPDATE modifie les valeurs de colonnes sur des lignes existantes.

UPDATE … SET … WHERE

UPDATE servers
SET    ip_address = '10.1.1.20'
WHERE  hostname = 'web-prod-01';

La clause SET définit les nouvelles valeurs. Le WHERE sélectionne les lignes à modifier.

Mettre à jour plusieurs colonnes

Un seul SET suffit pour modifier plusieurs colonnes : elles se séparent par des virgules, et non par des AND comme dans un WHERE. C'est une confusion fréquente, et elle produit un résultat silencieusement faux plutôt qu'une erreur.

UPDATE users
SET    role = 'admin', team = 'securite'
WHERE  username = 'lmoreau';

Les colonnes absentes du SET ne sont pas remises à leur valeur par défaut : elles gardent ce qu'elles contenaient. Un UPDATE ne réécrit que ce que vous listez, contrairement à un INSERT où les colonnes omises prennent leur défaut.

UPDATE avec sous-requête

Quand les lignes à modifier se définissent par rapport à une autre table, la sous-requête dans le WHERE évite d'avoir à recopier une liste d'identifiants à la main. Ici, on acquitte toutes les alertes des serveurs de l'environnement staging sans jamais écrire un seul server_id.

UPDATE alerts
SET    acknowledged = 1, acknowledged_at = datetime('now'), acknowledged_by = 2
WHERE  server_id IN (
    SELECT id FROM servers WHERE environment = 'staging'
);

Le réflexe de vérification vaut ici plus que partout ailleurs : exécutez d'abord la sous-requête seule (SELECT id FROM servers WHERE environment = 'staging') et comptez les lignes. Une sous-requête qui renvoie plus de résultats que prévu passe totalement inaperçue dans un UPDATE.

IN et valeurs NULL

Si la sous-requête peut renvoyer un NULL parmi ses résultats, IN continue de fonctionner, mais son opposé NOT IN ne renvoie alors plus aucune ligne. C'est un piège classique quand on inverse un filtre : préférez NOT EXISTS dès qu'une colonne nullable entre en jeu.

Le piège mortel : UPDATE sans WHERE

En SQL, le WHERE n'est pas obligatoire. Une requête sans filtre reste parfaitement valide : elle s'exécute sans avertissement et s'applique à l'intégralité de la table. Aucun moteur ne demande de confirmation, et la requête destructrice ne se distingue de la requête correcte que par une ligne manquante, la plus facile à oublier quand on rédige vite.

Les clients en ligne de commande n'aident pas non plus : sqlite3 affiche simplement le prompt suivant, sans indiquer combien de lignes ont été touchées. Le seul filet réellement efficace reste la transaction, décrite plus bas.

UPDATE sans WHERE = toutes les lignes modifiées

-- DANGER : modifie TOUTES les lignes de la table !
UPDATE users SET role = 'viewer';

Sans WHERE, chaque ligne de la table prend la nouvelle valeur. Il n'y a pas de confirmation, l'exécution est immédiate.

Réflexe de sécurité : avant tout UPDATE, écrivez d'abord un SELECT avec le même WHERE pour vérifier quelles lignes seront modifiées :

-- Étape 1 : vérifier
SELECT hostname, ip_address FROM servers WHERE hostname = 'web-prod-01';

-- Étape 2 : modifier (une fois le résultat validé)
UPDATE servers SET ip_address = '10.1.1.20' WHERE hostname = 'web-prod-01';

DELETE : supprimer des données

DELETE supprime des lignes d'une table. C'est l'ordre le plus dangereux du guide, parce que rien ne permet de retrouver les données une fois la transaction validée : il n'existe pas de corbeille en SQL, et une restauration passe forcément par une sauvegarde.

Beaucoup d'équipes remplacent d'ailleurs le DELETE par un effacement logique (UPDATE … SET active = 0) sur les tables sensibles, ce qui conserve la ligne et son historique. Le vrai DELETE reste réservé aux données réellement jetables, typiquement des journaux ou des alertes acquittées.

DELETE FROM … WHERE

DELETE ne prend pas de liste de colonnes : on supprime une ligne entière ou rien. Pour « effacer » une seule valeur, c'est un UPDATE … SET colonne = NULL qu'il faut écrire. Le WHERE obéit exactement aux mêmes règles que dans un SELECT, ce qui permet de le tester tel quel avant de supprimer.

DELETE FROM alerts
WHERE  acknowledged = 1 AND created_at < '2026-01-01';

Attention aux clés étrangères : si une autre table référence les lignes visées, la suppression échoue avec FOREIGN KEY constraint failed, ou emporte les lignes liées si la contrainte est déclarée ON DELETE CASCADE. Les deux comportements se valent, encore faut-il savoir lequel s'applique à votre schéma.

Supprimer toutes les lignes : DELETE vs TRUNCATE

Vider complètement une table se fait de deux façons, qui n'ont ni les mêmes performances ni les mêmes garanties. DELETE reste une opération de manipulation de données : chaque ligne est journalisée, donc annulable. TRUNCATE est une opération de structure, beaucoup plus rapide parce qu'elle ne journalise rien ligne à ligne, mais elle vous prive du retour arrière sur la plupart des moteurs.

Sur une table de quelques milliers de lignes, l'écart de vitesse est sans importance et DELETE reste le bon choix. Le débat ne devient réel qu'à partir de plusieurs millions de lignes.

	DELETE FROM table	TRUNCATE TABLE table
Vitesse	Lent (ligne par ligne)	Très rapide
Filtrable avec WHERE	Oui	Non
Annulable (ROLLBACK)	Oui	PostgreSQL : oui, MySQL et SQLite : non
Triggers déclenchés	Oui	Non

Glissez pour voir

SQLite ne supporte pas TRUNCATE

En SQLite, utilisez DELETE FROM table; sans WHERE pour vider une table. L'effet est le même, et c'est annulable dans une transaction.

Le piège mortel : DELETE sans WHERE

Même danger que UPDATE sans WHERE, toutes les lignes sont supprimées. Même réflexe : testez avec un SELECT d'abord.

RETURNING : récupérer les données modifiées

Depuis SQLite 3.35 (et PostgreSQL), RETURNING fonctionne sur INSERT, UPDATE et DELETE, ce qui permet de récupérer les lignes affectées sans requête supplémentaire :

-- Voir l'ID généré après insertion
INSERT INTO servers (hostname, ip_address, os, os_version, environment, datacenter, cpu_cores, ram_gb, disk_gb)
VALUES ('test-01', '10.4.1.10', 'Debian', '12', 'development', 'lyon-dc1', 2, 4, 50)
RETURNING id, hostname;

-- Voir ce qui a été modifié
UPDATE users
SET    email = 'lucas.moreau@infra.local'
WHERE  username = 'lmoreau'
RETURNING username, email;

-- Voir ce qui a été supprimé
DELETE FROM alerts
WHERE  severity = 'info' AND acknowledged = 1
RETURNING id, message;

MySQL ne supporte pas RETURNING

Avec MySQL, utilisez LAST_INSERT_ID() après un INSERT et ROW_COUNT() après un UPDATE ou DELETE.

Les transactions

Pourquoi les transactions sont indispensables

Une transaction regroupe plusieurs opérations SQL en un bloc atomique : soit toutes réussissent, soit aucune ne s'applique. C'est le filet de sécurité de toute modification en production.

Sans transaction explicite, chaque requête est automatiquement validée (autocommit). Si un UPDATE s'exécute mais qu'un INSERT suivant échoue, vous vous retrouvez dans un état incohérent.

BEGIN / COMMIT / ROLLBACK

Trois mots-clés suffisent. BEGIN ouvre la transaction et suspend la validation automatique. Les requêtes qui suivent modifient bien les données, mais ces modifications ne sont visibles que depuis votre propre connexion. COMMIT les rend définitives et visibles par tout le monde, ROLLBACK les efface comme si elles n'avaient jamais eu lieu.

L'exemple ci-dessous illustre le cas où la transaction est indispensable : deux UPDATE qui n'ont de sens que tous les deux ou aucun. Désactiver un serveur sans acquitter ses alertes laisse une supervision qui hurle sur une machine volontairement éteinte.

-- Démarrer une transaction
BEGIN;

-- Désactiver un serveur ET acquitter ses alertes
UPDATE servers SET active = 0 WHERE hostname = 'legacy-web-01';
UPDATE alerts  SET acknowledged = 1, acknowledged_at = datetime('now'), acknowledged_by = 1
WHERE  server_id = (SELECT id FROM servers WHERE hostname = 'legacy-web-01');

-- Tout est bon ? Valider.
COMMIT;

-- Un problème ? Tout annuler.
-- ROLLBACK;

Le réflexe BEGIN avant tout UPDATE en production

Prenez l'habitude de toujours ouvrir une transaction avant de modifier des données. Vous pouvez ensuite vérifier le résultat avec un SELECT, puis décider de COMMIT ou ROLLBACK.

BEGIN;
UPDATE servers SET ip_address = '10.1.1.20' WHERE hostname = 'web-prod-01';
-- Vérification :
SELECT hostname, ip_address FROM servers WHERE hostname = 'web-prod-01';
-- Si OK : COMMIT;
-- Sinon : ROLLBACK;

SAVEPOINT : points de sauvegarde intermédiaires

Dans une transaction longue, vous pouvez poser des points de sauvegarde pour ne revenir qu'à un endroit précis :

BEGIN;
UPDATE servers SET active = 0 WHERE created_at < '2024-01-01';
SAVEPOINT avant_delete;

DELETE FROM alerts WHERE server_id IN (
    SELECT id FROM servers WHERE active = 0
);
-- Oups, trop d'alertes supprimées ?
ROLLBACK TO avant_delete;
-- Les UPDATE sont conservés, le DELETE est annulé

COMMIT;

Autocommit : le comportement par défaut

Par défaut, SQLite, PostgreSQL et MySQL fonctionnent en mode autocommit : chaque requête est automatiquement validée dès son exécution. Dès que vous tapez BEGIN, l'autocommit est suspendu jusqu'au prochain COMMIT ou ROLLBACK.

Isolation des transactions

Les bases de données gèrent la lecture concurrente avec des niveaux d'isolation. Ils répondent tous à la même question : pendant que votre transaction est ouverte, que voyez-vous des modifications validées par les autres ? Voici les trois valeurs par défaut que vous rencontrerez :

Niveau	Comportement	Usage
READ COMMITTED (défaut PostgreSQL)	Chaque SELECT voit les données validées au moment de son exécution	Usage général
REPEATABLE READ (défaut MySQL InnoDB)	Tous les SELECT d'une transaction voient le même snapshot	Rapports cohérents
SERIALIZABLE (défaut SQLite)	Les transactions sont exécutées séquentiellement	Maximum de cohérence

Glissez pour voir

Pour vos exercices sur SQLite, l'isolation n'est pas un sujet, vous êtes le seul utilisateur. Elle devient critique en production multi-utilisateurs sur PostgreSQL ou MySQL.

Bonnes pratiques DML pour les admins

Ces cinq réflexes ne demandent aucun outil supplémentaire et coûtent quelques secondes par requête. Ils visent tous le même objectif : rendre une erreur détectable avant qu'elle soit définitive, plutôt que de compter sur la sauvegarde après coup.

Les deux premiers sont les plus rentables. Un SELECT de contrôle vous montre exactement les lignes ciblées, et une transaction vous laisse le droit de changer d'avis une fois que vous les avez vues.

1. Testez avec SELECT avant UPDATE ou DELETE. Écrivez d'abord un SELECT avec le même WHERE pour vérifier les lignes ciblées.

2. Encadrez toujours dans une transaction. BEGIN avant vos modifications, COMMIT après vérification, ROLLBACK en cas de doute.

3. Utilisez RETURNING pour voir immédiatement l'effet de votre requête.

4. Documentez vos modifications. Ajoutez un commentaire SQL avec le numéro de ticket : -- TICKET-1234 : correction IP web-prod-01.

5. Faites une sauvegarde avant les gros changements. Un cp infra.db infra.db.bak en SQLite, un pg_dump ou mysqldump en production.

Exercice pratique

Les trois exercices ci-dessous reprennent les trois ordres du guide dans l'ordre de risque croissant : une insertion (sans danger, elle n'écrase rien), une modification encadrée par une transaction, puis une suppression avec comptage préalable. La correction accompagne chaque étape, mais tapez la requête avant de la lire.

Ouvrez sqlite3 infra.db et pratiquez :

1. Insérez un nouveau serveur : cache-prod-01, IP 10.1.5.10, Debian 12, production, paris-dc1, 2 cores, 4 Go RAM, 50 Go disque.

   INSERT INTO servers (hostname, ip_address, os, os_version, environment, datacenter, cpu_cores, ram_gb, disk_gb)
   VALUES ('cache-prod-01', '10.1.5.10', 'Debian', '12', 'production', 'paris-dc1', 2, 4, 50)
   RETURNING id, hostname;

2. Ajoutez l'email manquant de Lucas Moreau dans une transaction.

   BEGIN;
   UPDATE users SET email = 'lucas.moreau@infra.local' WHERE username = 'lmoreau'
   RETURNING username, email;
   -- Vérifiez puis :
   COMMIT;

3. Supprimez les alertes info acquittées de plus de 7 jours, dans une transaction avec vérification.

   BEGIN;
   SELECT count(*) FROM alerts
   WHERE severity = 'info' AND acknowledged = 1 AND created_at < datetime('now', '-7 days');
   
   DELETE FROM alerts
   WHERE severity = 'info' AND acknowledged = 1 AND created_at < datetime('now', '-7 days')
   RETURNING id, message;
   -- Vérifiez puis COMMIT ou ROLLBACK

Dépannage

Les messages d'erreur de SQLite sont courts et sans contexte : ils nomment la contrainte violée, jamais la ligne fautive. Le tableau ci-dessous fait la traduction. Notez que les deux premières lignes signalent des contraintes qui font leur travail : la base vous protège d'une incohérence, ce n'est pas une panne à contourner mais une requête à corriger.

Symptôme	Cause probable	Solution
FOREIGN KEY constraint failed	Vous insérez/supprimez une ligne qui casse une référence FK	Vérifiez les dépendances avec un SELECT, supprimez les enfants avant le parent
UNIQUE constraint failed	La valeur existe déjà sur une colonne unique	Utilisez l'upsert (ON CONFLICT) ou vérifiez avant d'insérer
UPDATE modifie toutes les lignes	Pas de clause WHERE	Faites ROLLBACK immédiatement si vous êtes dans une transaction
database is locked	Une autre connexion sqlite3 tient un verrou	Fermez l'autre connexion ou attendez qu'elle finisse
Impossible d'annuler après COMMIT	COMMIT est définitif	Si vous n'avez pas fait BEGIN, l'autocommit a validé immédiatement

Glissez pour voir

À retenir

• INSERT, UPDATE et DELETE modifient les données, une fois validées, c'est irréversible
• Toujours écrire un SELECT de vérification avant un UPDATE ou DELETE
• Toujours utiliser une transaction (BEGIN / COMMIT / ROLLBACK) pour les modifications critiques
• Un UPDATE ou DELETE sans WHERE affecte toutes les lignes de la table
• RETURNING (SQLite 3.35+, PostgreSQL) économise une requête de vérification après modification
• ON CONFLICT (upsert) évite les erreurs de doublons sur les contraintes uniques

Prochaines étapes

Jointures et sous-requêtes Croisez les données de plusieurs tables avec JOIN, sous-requêtes et EXISTS

Fonctions et agrégations COUNT, SUM, GROUP BY pour calculer des statistiques sur vos données

Administrer PostgreSQL Passez à la production avec PostgreSQL : installation, sécurisation, sauvegardes

×

📖 Voir la documentation →