Skip to content
aviral gupta
Get in touch

// B4.1 · ~30 min · Beginner

UPDATE and DELETE

After this lesson you can change exactly the rows you mean, remove rows that are no longer needed, and check the result before you trust it.

Lesson 1 of 5 in B4 Changing data and schema

Start of the module

You will be able to

  • Change chosen rows with UPDATE … SET … WHERE, several columns at once and from their old values
  • Predict which rows a change touches: none, some or all, and why = NULL matches nothing
  • Remove rows with DELETE … WHERE, empty a table with DELETE or TRUNCATE, and update from another table

  1. Warm-up · Activity 1 of 7

    Warm-up: Linus brings Dune back. Which statement records that in loans, where his loan has id 4?

  2. Predict · Activity 2 of 7

    Predict before you read on. Margaret wants to move to Hamburg, but the WHERE was forgotten. Ada and Linus live in Berlin, Grace in Munich, Margaret’s city is NULL. How many members live in Hamburg afterwards?

    UPDATE members
SET city = 'Hamburg';

  3. Practice · Activity 3 of 7

    Neuromancer (id 6) has 1 copy. Fill in the gap so that the library gets one more copy of it.

    UPDATE books
SET copies = ____ + 1
WHERE id = 6;
    UPDATE books SET copies = + 1 WHERE id = 6;

  4. Practice · Activity 4 of 7

    Match each statement to what it does to the table loans.

  5. Practice · Activity 5 of 7

    Put the lines of this change in order: Linus (id 3) moves to Hamburg and gets the e-mail address linus@example.com.

    1. 1.WHERE id = 3;
    2. 2. email = 'linus@example.com'
    3. 3.SET city = 'Hamburg',
    4. 4.UPDATE members

  6. Brain teaser · Activity 6 of 7

    Brain teaser. Ada and Linus live in Berlin, Grace in Munich, Margaret’s city is NULL. How many members live in Hamburg after this?

    UPDATE members
SET city = 'Hamburg'
WHERE city <> 'Berlin';

  7. Apply · Activity 7 of 7

    Mini-task. Three changes at the desk, one statement each: Grace returns Neuromancer (loan 5) on 2026-10-03; Linus (id 3) gets the e-mail address linus@example.com and moves to Munich; then Grace’s returned loans are removed. Before each statement, write down which rows it should touch, and check with a SELECT afterwards.

    Check your work against this list

Build it yourself

Read the worked example, then write the exercises. Your code runs in your browser or on your computer and is never uploaded.

Worked example

A day at the library desk

setup.sql loads the library of module B1: books, members and loans. main.sql records a return, adds copies, changes two columns of one member, and removes the loans that have come back, with a SELECT after each change to show its effect. On your computer: psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql.

main.sql

-- 1. Linus brings Dune back (loan 4).
UPDATE loans
SET returned_on = '2026-10-01'
WHERE id = 4;

-- 2. One more copy of every sci-fi book.
UPDATE books
SET copies = copies + 1
WHERE genre = 'sci-fi';

SELECT id, title, copies
FROM books
ORDER BY id;

-- 3. Two columns at once: Margaret moves and changes her address.
UPDATE members
SET city = 'Hamburg', email = 'maggie@example.com'
WHERE id = 4;

SELECT id, name, email, city
FROM members
ORDER BY id;

-- 4. Remove the loans that have come back.
DELETE FROM loans
WHERE returned_on IS NOT NULL;

SELECT id, book_id, member_id, loaned_on
FROM loans
ORDER BY id;

setup.sql

CREATE TABLE books (
  id integer,
  title text,
  author text,
  year integer,
  genre text,
  copies integer
);

INSERT INTO books VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

CREATE TABLE members (
  id integer,
  name text,
  email text,
  city text,
  joined date
);

INSERT INTO members VALUES
  (1, 'Ada', 'ada@example.com', 'Berlin', '2025-01-15'),
  (2, 'Grace', 'grace@example.com', 'Munich', '2025-03-02'),
  (3, 'Linus', NULL, 'Berlin', '2026-02-10'),
  (4, 'Margaret', 'margaret@example.com', NULL, '2026-05-20');

CREATE TABLE loans (
  id integer,
  book_id integer,
  member_id integer,
  loaned_on date,
  returned_on date
);

INSERT INTO loans VALUES
  (1, 1, 1, '2026-09-01', '2026-09-10'),
  (2, 3, 1, '2026-09-12', NULL),
  (3, 2, 2, '2026-09-05', '2026-09-20'),
  (4, 1, 3, '2026-09-15', NULL),
  (5, 6, 2, '2026-09-18', NULL);

Run it with

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

Output

 id |    title    | copies
----+-------------+--------
  1 | Dune        |      3
  2 | Emma        |      1
  3 | Kindred     |      2
  4 | Beloved     |      1
  5 | The Hobbit  |      3
  6 | Neuromancer |      2
(6 rows)

 id |   name   |       email        |  city
----+----------+--------------------+---------
  1 | Ada      | ada@example.com    | Berlin
  2 | Grace    | grace@example.com  | Munich
  3 | Linus    |                    | Berlin
  4 | Margaret | maggie@example.com | Hamburg
(4 rows)

 id | book_id | member_id | loaned_on
----+---------+-----------+------------
  2 |       3 |         1 | 2026-09-12
  5 |       6 |         2 | 2026-09-18
(2 rows)
  • UPDATE and DELETE print nothing here, because psql runs quietly (-q); without -q it would print tags such as UPDATE 3.
  • copies = copies + 1 reads each sci-fi book’s old value: Dune goes from 2 to 3, Kindred and Neuromancer from 1 to 2. The other books keep theirs.
  • One UPDATE changes Margaret’s city and email, separated by a comma; Linus’s NULL email stays NULL, because his row does not match WHERE id = 4.
  • Step 1 returned loan 4, so step 4 removes loans 1, 3 and 4: only the two open loans are left.
Change it and run it

Tab indents and Shift+Tab outdents. To leave the editor with the keyboard, press Esc, then Tab.

The first run downloads PostgreSQL for your browser (up to 10.1 MB) and keeps it cached. Every run starts from an empty database. Your code stays on your device.

Exercises

Exercise 1 of 2

Three changes at the desk

Write three UPDATE statements. First, Margaret (id 4) moves to Hamburg. Second, Grace returns Neuromancer: loan 5 gets the return date 2026-10-02. Third, the library buys one more copy of every book published before 1950. Change nothing else.

Tab indents and Shift+Tab outdents. To leave the editor with the keyboard, press Esc, then Tab.

The first run downloads PostgreSQL for your browser (up to 10.1 MB) and keeps it cached. Every run starts from an empty database. Your code stays on your device.

Hints
  1. Hint 1

    Each UPDATE needs a WHERE that picks exactly the rows meant: id = 4 for Margaret, id = 5 for the loan.

  2. Hint 2

    Dates and text go in single quotes: SET returned_on = '2026-10-02'.

  3. Hint 3

    For the books, use the old value: SET copies = copies + 1 WHERE year < 1950.

Show a solution

One way to solve it. Yours can look different and still pass the checks.

UPDATE members
SET city = 'Hamburg'
WHERE id = 4;

UPDATE loans
SET returned_on = '2026-10-02'
WHERE id = 5;

UPDATE books
SET copies = copies + 1
WHERE year < 1950;
Run it on your computer

Install PostgreSQL 18 or newer. Save these files in one folder, open a terminal in that folder, and run the commands below.

main.sql

-- setup.sql has loaded books, members and loans.
-- Write the three UPDATE statements here.
SELECT id, name, city FROM members ORDER BY id;

test.sql

-- test: Margaret lives in Hamburg, and the other members kept their cities
SELECT string_agg(coalesce(city, '-'), ',' ORDER BY id) = 'Berlin,Munich,Berlin,Hamburg' FROM members;

-- test: Loan 5 was returned on 2026-10-02
SELECT count(*) = 1 FROM loans WHERE id = 5 AND returned_on = '2026-10-02';

-- test: Loans 2 and 4 are still open
SELECT string_agg(id::text, ',' ORDER BY id) = '2,4' FROM loans WHERE returned_on IS NULL;

-- test: Emma and The Hobbit have one copy more, the other books are unchanged
SELECT string_agg(copies::text, ',' ORDER BY id) = '2,2,1,1,4,1' FROM books;

setup.sql

CREATE TABLE books (
  id integer,
  title text,
  author text,
  year integer,
  genre text,
  copies integer
);

INSERT INTO books VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

CREATE TABLE members (
  id integer,
  name text,
  email text,
  city text,
  joined date
);

INSERT INTO members VALUES
  (1, 'Ada', 'ada@example.com', 'Berlin', '2025-01-15'),
  (2, 'Grace', 'grace@example.com', 'Munich', '2025-03-02'),
  (3, 'Linus', NULL, 'Berlin', '2026-02-10'),
  (4, 'Margaret', 'margaret@example.com', NULL, '2026-05-20');

CREATE TABLE loans (
  id integer,
  book_id integer,
  member_id integer,
  loaned_on date,
  returned_on date
);

INSERT INTO loans VALUES
  (1, 1, 1, '2026-09-01', '2026-09-10'),
  (2, 3, 1, '2026-09-12', NULL),
  (3, 2, 2, '2026-09-05', '2026-09-20'),
  (4, 1, 3, '2026-09-15', NULL),
  (5, 6, 2, '2026-09-18', NULL);

psql runs the files on your own PostgreSQL server. Use an empty database you can throw away: add -d and its name to the command.

Run the program:

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

There is no command for the checks on your computer yet. They are in test.sql: each check query prints t when it passes.

Exercise 2 of 2

Clearing up the loans

Write three statements, in this order. First, delete the loans that have been returned. Second, the library withdraws Beloved: delete the book with id 4. Third, Ada (member 1) returns everything she still has: give her open loans the return date 2026-10-03.

Tab indents and Shift+Tab outdents. To leave the editor with the keyboard, press Esc, then Tab.

The first run downloads PostgreSQL for your browser (up to 10.1 MB) and keeps it cached. Every run starts from an empty database. Your code stays on your device.

Hints
  1. Hint 1

    Returned loans have a return date: DELETE FROM loans WHERE returned_on IS NOT NULL.

  2. Hint 2

    Delete the book by its id, so no other book can match: DELETE FROM books WHERE id = 4.

  3. Hint 3

    Ada's open loans: WHERE member_id = 1 AND returned_on IS NULL. Keep the order: if this UPDATE ran first, the DELETE would remove loan 2 too.

Show a solution

One way to solve it. Yours can look different and still pass the checks.

DELETE FROM loans
WHERE returned_on IS NOT NULL;

DELETE FROM books
WHERE id = 4;

UPDATE loans
SET returned_on = '2026-10-03'
WHERE member_id = 1 AND returned_on IS NULL;
Run it on your computer

Install PostgreSQL 18 or newer. Save these files in one folder, open a terminal in that folder, and run the commands below.

main.sql

-- setup.sql has loaded books, members and loans.
-- Write the three statements here.
SELECT id, member_id, returned_on FROM loans ORDER BY id;

test.sql

-- test: Only loans 2, 4 and 5 are left
SELECT string_agg(id::text, ',' ORDER BY id) = '2,4,5' FROM loans;

-- test: Beloved is gone, and the other five books are still there
SELECT string_agg(id::text, ',' ORDER BY id) = '1,2,3,5,6' FROM books;

-- test: Ada's loan 2 was returned on 2026-10-03
SELECT count(*) = 1 FROM loans WHERE id = 2 AND returned_on = '2026-10-03';

-- test: Loans 4 and 5 of the other members are still open
SELECT string_agg(id::text, ',' ORDER BY id) = '4,5' FROM loans WHERE returned_on IS NULL;

setup.sql

CREATE TABLE books (
  id integer,
  title text,
  author text,
  year integer,
  genre text,
  copies integer
);

INSERT INTO books VALUES
  (1, 'Dune', 'Frank Herbert', 1965, 'sci-fi', 2),
  (2, 'Emma', 'Jane Austen', 1815, 'classic', 1),
  (3, 'Kindred', 'Octavia E. Butler', 1979, 'sci-fi', 1),
  (4, 'Beloved', 'Toni Morrison', 1987, 'fiction', 1),
  (5, 'The Hobbit', 'J. R. R. Tolkien', 1937, 'fantasy', 3),
  (6, 'Neuromancer', 'William Gibson', 1984, 'sci-fi', 1);

CREATE TABLE members (
  id integer,
  name text,
  email text,
  city text,
  joined date
);

INSERT INTO members VALUES
  (1, 'Ada', 'ada@example.com', 'Berlin', '2025-01-15'),
  (2, 'Grace', 'grace@example.com', 'Munich', '2025-03-02'),
  (3, 'Linus', NULL, 'Berlin', '2026-02-10'),
  (4, 'Margaret', 'margaret@example.com', NULL, '2026-05-20');

CREATE TABLE loans (
  id integer,
  book_id integer,
  member_id integer,
  loaned_on date,
  returned_on date
);

INSERT INTO loans VALUES
  (1, 1, 1, '2026-09-01', '2026-09-10'),
  (2, 3, 1, '2026-09-12', NULL),
  (3, 2, 2, '2026-09-05', '2026-09-20'),
  (4, 1, 3, '2026-09-15', NULL),
  (5, 6, 2, '2026-09-18', NULL);

psql runs the files on your own PostgreSQL server. Use an empty database you can throw away: add -d and its name to the command.

Run the program:

psql -X -q -v ON_ERROR_STOP=1 -f setup.sql -f main.sql

There is no command for the checks on your computer yet. They are in test.sql: each check query prints t when it passes.

Common mistakes

A text value without quotes

UPDATE books
SET copies = copies + 1
WHERE title = Dune;

What psql prints

ERROR:  column "dune" does not exist

Why, and the fix

Without quotes, Dune is read as a name, folded to lower case, and looked up as a column. Text and dates are values in single quotes: WHERE title = 'Dune'. The same holds in SET: SET city = 'Hamburg'.

AND between two assignments

UPDATE books
SET copies = 2 AND year = 1966
WHERE id = 1;

What psql prints

ERROR:  argument of AND must be type boolean, not type integer

Why, and the fix

In SET, AND does not separate assignments; it becomes part of the value, copies = (2 AND year = 1966), and 2 is no boolean. Separate assignments with a comma: SET copies = 2, year = 1966. AND belongs in WHERE, where it combines conditions.

The table name in front of a SET column

UPDATE books
SET books.copies = 3
WHERE id = 1;

What psql prints

ERROR:  column "books" of relation "books" does not exist

Why, and the fix

The column after SET always belongs to the table being updated, so it takes no table name; PostgreSQL reads books.copies as a field copies of a column books. Write SET copies = 3. In WHERE and in the expressions on the right, qualified names such as books.copies are fine.

PostgreSQL in the browser: PGlite 0.5.8 (PostgreSQL 18.3), Apache-2.0 and PostgreSQL License. Licence and source

Exit ticket

5 questions, no hints. Score 80% or more to complete the lesson.

Finish every activity above to unlock the exit ticket.

Report a problem

Spotted something wrong or unclear? Say what, and it will be checked and fixed.

#

At least 20 characters.

Only if you want a reply.

Key ideas

UPDATE changes values in rows that exist

UPDATE loans SET returned_on = '2026-10-01' WHERE id = 4; names the table, the column with its new value, and the rows to change. Columns you do not name in SET keep their values. To change several columns, separate the assignments with commas: SET city = 'Hamburg', email = 'maggie@example.com'. The new value can be any expression, also one using the old value: SET copies = copies + 1 adds a copy. Every expression in SET sees the row as it was before the update, so SET name = city, city = name swaps the two. Text and dates need single quotes, as in INSERT.

WHERE decides how many rows change

Only rows for which the WHERE condition is true change. That can be one row, several, or none: an UPDATE that matches nothing is not an error, it simply changes nothing. Leave WHERE out and every row of the table changes, which is rarely what you meant. A condition that is never true is the quiet opposite: city = NULL is NULL for every row, so WHERE city = NULL changes nothing; write city IS NULL. psql reports a tag such as UPDATE 3 with the number of rows, but not in quiet mode (-q). So run a SELECT with the same WHERE first, and look at the rows afterwards.

DELETE removes whole rows

DELETE FROM loans WHERE returned_on IS NOT NULL; removes the rows that match, always whole rows; to clear one value, UPDATE it to NULL instead. Without WHERE, DELETE FROM loans; removes every row, and the table stays, valid and empty. TRUNCATE loans; does the same faster, because it does not scan the table; DROP TABLE would remove the table itself. Neither has an undo once it has run (outside a transaction, which comes in module I4). Changes can also depend on another table: UPDATE loans SET … FROM members WHERE members.id = loans.member_id AND members.city = 'Berlin' changes the loans of Berlin members.

Sources

Last reviewed October 3, 2026