UPDATE

UPDATE and DELETE

Module 9 of 9 SQLite in your browser

Lesson concept What you'll do

1 / 4

Change or remove the rows that match a test.

UPDATE ... SET changes values and DELETE removes rows. Always add a WHERE.

Next: Check the rows first

On your own New data, same idea

Tidy up the loans: delete every loan that has been returned (its returned date isn't empty). Loans still out must stay.

The database

11 rows

loan_id	book_id	member_id	loan_date	returned
1	1	102	2026-01-02	2026-01-22
2	3	101	2026-02-10	2026-02-17
3	10	106	2026-03-05	2026-03-21
4	8	101	2026-04-07	2026-04-28
5	6	102	2026-05-20	2026-06-03
6	4	104	2026-06-22	2026-06-29
7	4	106	2026-08-02	2026-08-20
8	7	104	2026-08-11	2026-09-01
9	2	104	2026-09-09	NULL
10	5	105	2026-09-15	NULL
11	3	106	2026-10-28	NULL

Your query Ctrl + Enter runs it

Hint: click the green edit button on the board, or type it yourself

loans after your query Ready to run

Run your query to see the result table.

Press the green Run button or Ctrl+Enter.

Study notes

How UPDATE, DELETE works

UPDATE changes the genre, but only in the rows WHERE picks: books before 1900.

UPDATE books
SET genre = 'Classic'
WHERE year < 1900;

books before

book_id	title	genre	year
1	Frankenstein	Horror	1818
2	Emma	Romance	1815
3	Dune	Science Fiction	1965
4	Carrie	Horror	1974

Highlighted: the rows WHERE picks

books after (2 rows changed)

book_id	title	genre	year
1	Frankenstein	HorrorClassic	1818
2	Emma	RomanceClassic	1815
3	Dune	Science Fiction	1965
4	Carrie	Horror	1974

How do I change values?

UPDATE books SET genre = 'Classic' WHERE year < 1900;
SET says the new value; WHERE says which rows. Several columns at once: SET genre = 'Classic', pages = 300.
How do I remove rows?

DELETE FROM loans WHERE returned IS NOT NULL;
It removes every row the WHERE picks. There's no column list: whole rows go.
What if I forget WHERE?

UPDATE changes every row and DELETE empties the table. In real databases there's no undo button, so this is the classic expensive mistake.
How do I stay safe?

Run a SELECT * ... WHERE with the same condition first and check the rows. In real work, wrap changes in BEGIN ... COMMIT, so ROLLBACK can undo them.
Is anything saved here?

No: every run starts again from the original table, so practise freely.

Other databases

The lessons run on SQLite. Most of SQL is the same everywhere; these are the places where this lesson's SQL differs.

Do changes save straight away?
Database	How	Note
SQLite (these lessons)	`Yes`	Unless you start with BEGIN; then COMMIT saves and ROLLBACK undoes.
PostgreSQL	`Yes`	Unless you start with BEGIN; then COMMIT or ROLLBACK.
MySQL / MariaDB	`Yes`	autocommit is on. START TRANSACTION, then COMMIT or ROLLBACK.
SQL Server	`Yes`	Unless you start with BEGIN TRANSACTION; then COMMIT or ROLLBACK.
Oracle	`No`	Changes wait for COMMIT, and ROLLBACK undoes them. Most tools don't commit for you.
Sybase ASE	`Yes`	Unless you start with BEGIN TRANSACTION (or turn chained mode on).

Know someone fighting with a spreadsheet? Send them this page.