Modifying data: INSERT, UPDATE, DELETE

Add, modify and delete rows safely, avoiding the classic trap of the forgotten WHERE.

8 min · 3 questions

So far, you have only read data. Let's move on to writing. INSERT INTO adds a row: you list the columns between parentheses, then the matching values after VALUES, in the same order. Naming the columns explicitly is a good habit: the query stays valid even if the table's structure evolves.

SQL

INSERT INTO books (id, title, author, genre, year, price)
VALUES (8, 'Ravage', 'Barjavel', 'sf', 1943, 7.5);

SELECT * FROM books WHERE id = 8;

Adding an 8th book, then checking

UPDATE modifies existing rows: after SET, you indicate the column and its new value; after WHERE, you specify which rows are affected. This WHERE is crucial: it is what targets the change.

SQL

UPDATE books SET price = 8.9 WHERE id = 8;

SELECT title, price FROM books WHERE id = 8;

The price of Ravage goes from 7.5 to 8.9

An UPDATE or a DELETE without a WHERE clause applies to ALL the rows in the table: DELETE FROM books; empties the entire table, with no confirmation and no way back once the transaction is committed. It is the costliest mistake in SQL: always re-read your WHERE before running the query.

DELETE FROM removes rows. As with UPDATE, the WHERE clause designates the rows to delete; the others stay intact. Let's delete the book we just added, then check the count.

SQL

DELETE FROM books WHERE id = 8;

SELECT COUNT(*) FROM books;

Back to 7 rows: the original table is intact

Two safety reflexes: first, test your condition with a SELECT using the same WHERE to see exactly which rows will be affected; second, wrap risky changes in a transaction: BEGIN; opens the transaction, ROLLBACK; cancels everything, COMMIT; commits it for good.

SQL

BEGIN;
DELETE FROM books WHERE genre = 'sf';
SELECT COUNT(*) FROM books;
ROLLBACK;
SELECT COUNT(*) FROM books;

3 rows during the transaction, 7 after ROLLBACK

Knowledge check

Make sure you remember the key points of this lesson.

What does the query DELETE FROM books; do when run without WHERE?
- It deletes the first row of the table
- It deletes all the rows of the table
- It causes a syntax error
Which query changes the price of the book with id 3 to 6.9?
- UPDATE books SET price = 6.9 WHERE id = 3;
- INSERT INTO books SET price = 6.9;
- SELECT price = 6.9 FROM books WHERE id = 3;
In a transaction, what is ROLLBACK for?
- To commit the changes for good
- To cancel all the changes made since BEGIN
- To delete the affected table