Modifier les données : INSERT, UPDATE, DELETE

INSERT

Nommez les colonnes, puis donnez une valeur pour chacune. Les colonnes omises prennent leur valeur par défaut — ici added_at vaut par défaut l’horodatage courant.

INSERT INTO wishlists (customer_id, product_id)
VALUES (1, 288);

Une ligne modifiée. Nommer explicitement les colonnes, au lieu de se fier à leur ordre dans la table, est ce qui fait que l’instruction continue de fonctionner après l’ajout d’une colonne par quelqu’un d’autre.

Ce que la base refuse

Exécutez exactement la même instruction une seconde fois.

-- The same statement a second time. (customer_id, product_id) is the
-- primary key of wishlists, so the database refuses the duplicate.
INSERT INTO wishlists (customer_id, product_id)
VALUES (1, 288);

Une erreur, et rien n’a changé. wishlists a PRIMARY KEY (customer_id, product_id) : la même paire ne peut donc pas exister deux fois, la contrainte l’a refusée. Les contraintes ne sont pas des obstacles à contourner ; ce sont elles qui empêchent un bug de votre application de devenir des données durablement fausses.

UPDATE, et la clause qui compte

UPDATE customers
SET loyalty_points = loyalty_points + 100
WHERE id = 1;

Deux habitudes rendent cela beaucoup plus difficile. La première : écrire la clause WHERE avant la clause SET. La seconde : l’exécuter d’abord comme un SELECT — même FROM, même WHERE — et regarder ce qui revient avant de modifier quoi que ce soit.

Planche peut faire la seconde pour vous. Activez Paramètres › Sécurité › Aperçu des lignes affectées et un UPDATE ou un DELETE est réécrit en SELECT équivalent, compté et affiché avant de s’exécuter. Une connexion en lecture seule et une couleur par connexion pour la production se trouvent au même endroit.

SELECT id, first_name, last_name, loyalty_points
FROM customers
WHERE id = 1;

DELETE

DELETE FROM wishlists
WHERE customer_id = 1
  AND product_id = 288;

Choisir les lignes avec une sous-requête

La clause WHERE d’un UPDATE ou d’un DELETE est une clause WHERE ordinaire : tout ce qui vient de la leçon sur les sous-requêtes s’applique. Une sous-requête, c’est ainsi qu’on modifie des lignes d’une table en fonction du contenu d’une autre.

-- Which rows to change is its own question, and a subquery can answer it.
UPDATE customers
SET loyalty_points = loyalty_points + 50
WHERE id IN (
  SELECT customer_id
  FROM orders
  WHERE status = 'refunded'
    AND total > 2000
);

242 clients avaient une commande remboursée de plus de 2 000, et ces 242 lignes ont changé. L’habitude vue plus haut s’applique exactement : exécutez d’abord SELECT id FROM customers WHERE id IN (...) et comptez les lignes, car c’est dans la sous-requête que se glissera l’erreur.

DELETE FROM wishlists
WHERE product_id IN (
  SELECT id FROM products WHERE stock_quantity = 0
);

NOT EXISTS et EXISTS fonctionnent ici aussi, et sont le choix le plus sûr pour le cas nié, pour la raison donnée dans la leçon sur les sous-requêtes. Ce qui n’est pas portable, c’est de joindre directement dans un UPDATE ou un DELETE — chaque moteur l’écrit autrement, et l’un d’eux ne sait pas le faire du tout. La page de dialecte consacrée au sujet couvre les cinq.

Les transactions

Une transaction regroupe des instructions de sorte qu’elles se produisent toutes ou aucune — et, tant que vous n’avez pas validé, elle vous laisse regarder le résultat et changer d’avis.

BEGIN;

UPDATE coupons
SET used_count = used_count + 1
WHERE code = 'WELCOME10';

SELECT code, used_count FROM coupons WHERE code = 'WELCOME10';

ROLLBACK;

BEGIN l’ouvre, le SELECT montre le bon déjà incrémenté, et ROLLBACK remet tout en place. Remplacez ROLLBACK par COMMIT et la modification devient définitive. Vérifiez vous-même que le compte est inchangé :

SELECT code, used_count FROM coupons WHERE code = 'WELCOME10';

C’est le bon outil pour une modification risquée sur une vraie base : ouvrir une transaction, exécuter l’instruction, vérifier que le nombre de lignes correspond à ce que vous attendiez, et seulement alors valider. Planche en fait un mode — Connexion › Transactions manuelles maintient une transaction ouverte et place Valider et Annuler dans la barre d’outils, de sorte que chaque instruction reste provisoire jusqu’à ce que vous tranchiez.

Ce que vous savez faire maintenant

Tout, sauf découvrir pourquoi une requête est lente — c’est la dernière leçon.