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…

• 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.

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

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

UPDATE avec sous-requête

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

Le piège mortel : UPDATE sans WHERE

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.

DELETE FROM … WHERE

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

Supprimer toutes les lignes : DELETE vs TRUNCATE

	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

-- 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. Deux niveaux courants :

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

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

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

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 →