SQL INSERT, UPDATE and DELETE: change the data

So far, every query read data without changing anything. Three statements let you change it: INSERT adds rows, UPDATE changes existing values and DELETE removes rows. They are powerful… and dangerous: a forgotten condition can change a whole table at once.

Syntax

INSERT INTO table (col1, col2) VALUES (value1, value2);
UPDATE table SET col1 = value WHERE condition;
DELETE FROM table WHERE condition;

How it works

INSERT: add rows

List the columns to fill, then the values in the same order. Columns you leave out get their default value, or NULL. You can insert several rows at once: VALUES (…), (…), (…).

UPDATE: change rows

SET gives the new values, possibly computed from the old ones (prix = prix * 1.1). The WHERE says which rows to change. Without WHERE, every row of the table is changed.

DELETE: remove rows

Same caution: without WHERE, the whole table is emptied. When rows reference others (an order line points to its order), delete the “child” rows first.

The good habit

Before an UPDATE or a DELETE, run a SELECT with the same WHERE: you see exactly which rows will be affected. In a real application, changes are grouped in a transaction (BEGIN … COMMIT), which can be cancelled with ROLLBACK if something goes wrong.

Diagram

Simplified diagram to show the idea. The examples below use the real SQL Quest practice database (a fictional online shop: see the full schema).

Examples with their results

INSERT INTO fournisseurs (id, nom, pays)
VALUES (8, 'NordicHome', 'Suède');

SELECT * FROM fournisseurs;

UPDATE produits
SET prix = ROUND(prix * 1.1, 2)
WHERE categorie = 'Livres';

SELECT nom, prix FROM produits WHERE categorie = 'Livres';

DELETE FROM lignes_commande
WHERE commande_id IN (SELECT id FROM commandes WHERE statut = 'annulée');

DELETE FROM commandes WHERE statut = 'annulée';

SELECT statut, COUNT(*) AS nb FROM commandes GROUP BY statut;

Common mistakes

UPDATE produits SET stock = 0;

UPDATE produits SET stock = 0 WHERE id = 3;

INSERT INTO fournisseurs (id, nom, pays) VALUES (9, 'Test');

INSERT INTO fournisseurs (id, nom, pays) VALUES (9, 'Test', 'France');

INSERT INTO fournisseurs (id, nom, pays) VALUES (1, 'Doublon', 'France');

INSERT INTO fournisseurs (nom, pays) VALUES ('Nouveau', 'France');

Exercises

Try writing the query yourself before opening the solution.

UPDATE produits SET stock = 0 WHERE fournisseur_id IN (SELECT id FROM fournisseurs WHERE pays = 'Chine');
SELECT nom, stock FROM produits WHERE stock = 0;

INSERT INTO clients (prenom, nom, ville) VALUES ('Nina', 'Petit', 'Lyon'), ('Paul', 'Morel', 'Nice');
SELECT COUNT(*) FROM clients;

Frequently asked questions