UPDATE and DELETE, and the WHERE you must not forget

UPDATE changes rows. DELETE removes them. Both take a WHERE, both run instantly with no confirmation, and both will happily do it to every row in the table if you let them.

`UPDATE`

UPDATE books SET copies = 5 WHERE id = 1;
UPDATE books SET copies = copies + 1 WHERE shelf = 'F-01';
UPDATE loans SET returned_on = current_date, fine_paise = 0 WHERE id = 42;

Several columns in one SET, comma-separated. The right-hand side can refer to the current value, which is how copies + 1 works.

copies = copies + 1 is safer than reading the value and writing it back, because the database does the arithmetic on whatever is there now. That distinction is the entire concurrency lesson three lessons from here, and it starts as a habit.

`DELETE`

DELETE FROM loans WHERE id = 42;
DELETE FROM loans WHERE returned_on < DATE '2020-01-01';

DELETE FROM — there is no column list, because you are removing whole rows.

The one that ruins an afternoon

UPDATE books SET copies = 0;      -- every book
DELETE FROM loans;                -- every loan

Both are valid. Both run immediately. Neither asks.

Three habits, and they cost nothing:

1. Write the WHERE in the same breath as the verb. Type DELETE FROM loans WHERE id = 42; as one thought. Never type DELETE FROM loans and then go back to add the condition — that is the version that gets executed by a stray Enter.

2. Run it as a SELECT first.

SELECT * FROM loans WHERE returned_on < DATE '2020-01-01';   -- look at these
DELETE FROM loans WHERE returned_on < DATE '2020-01-01';     -- then change the verb

Same WHERE, so you are looking at exactly the rows that will go.

3. Wrap it in a transaction you can abandon.

BEGIN;
DELETE FROM loans WHERE returned_on < DATE '2020-01-01';
-- DELETE 37   ← is that the number you expected?
ROLLBACK;      -- or COMMIT if it is

The row count is your confirmation dialogue. If you expected 37 and it says 1,204, you have just avoided an incident — and ROLLBACK undoes it completely.

Get into the habit of BEGIN before any unguarded write on real data. It is free.

`RETURNING` on a write

UPDATE d1 SET n = n * 10 WHERE n > 1 RETURNING id, n;

 id | n
----+----
  2 | 20
  3 | 30
(2 rows)
UPDATE 2

See exactly what changed, in the same statement. Far better than counting rows and hoping.

DELETE FROM loans WHERE id = 42 RETURNING *;

RETURNING * on a DELETE gives you the row you just removed — the only chance you get to look at it.

This is also how you archive-and-delete atomically:

WITH removed AS (
  DELETE FROM loans WHERE returned_on < DATE '2020-01-01' RETURNING *
)
INSERT INTO archived_loans SELECT * FROM removed;

One statement. The rows cannot be deleted without being archived, because both happen or neither does.

`UPDATE ... FROM`

Updating one table using values from another:

UPDATE books b
SET    copies = b.copies + t.received
FROM   deliveries t
WHERE  t.book_id = b.id AND t.processed_on = current_date;

The FROM names the other table and the WHERE joins them. This is the standard way to apply a batch of changes, and it is one statement rather than a loop.

The warning: if the FROM side has more than one matching row, PostgreSQL picks one arbitrarily and applies it — no error. Aggregate first if that is possible:

FROM (SELECT book_id, sum(received) AS received FROM deliveries GROUP BY book_id) t

Delete versus soft delete

DELETE FROM members WHERE id = 5;                                  -- gone
UPDATE members SET deleted_at = now() WHERE id = 5;                -- gone-ish

A soft delete marks the row instead of removing it. Every query then needs WHERE deleted_at IS NULL, which is the cost, and forgetting it in one place is the classic bug.

Use a soft delete when the record matters: an order, a loan, anything you might need for an audit or a dispute. Use a real delete for genuinely transient data.

And note the middle option this course prefers for history: do not delete at all, add a status. A cancelled loan is a loan with a status, not an absent row. The Full-Stack course's rule — the money moved twice and both movements are part of the record — applies to anything somebody might ask about later.

`TRUNCATE`

TRUNCATE loans;
TRUNCATE loans, book_categories;
TRUNCATE loans RESTART IDENTITY;
TRUNCATE members CASCADE;             -- and everything referencing it. Careful.

Removes every row, much faster than DELETE — it does not scan or log each row, it simply discards the storage.

Three things to know:

No WHERE. All of it, or use DELETE.
It is transactional in PostgreSQL, so BEGIN; TRUNCATE ...; ROLLBACK; works. That is not true in MySQL, where it commits implicitly.
CASCADE truncates every referencing table too. On a schema with foreign keys, that can be most of your database.

Use it for test fixtures and for emptying a staging table. Think twice anywhere else.

The row count is information

UPDATE 0
DELETE 1204

UPDATE 0 means your WHERE matched nothing, which is frequently a bug — a wrong id, a row somebody else already changed, a NULL comparison. Application code should check it: "update this row if it is still pending" returning 0 means it was not pending, and that is a fact you need.

That check is the basis of the compare-and-swap pattern in the concurrency lesson.

Updates are not free

A brief mention of something module 7 explains: PostgreSQL does not modify a row in place. An UPDATE writes a new version of the row and marks the old one dead, and VACUUM reclaims the space later.

Two consequences worth knowing now:

Updating one column rewrites the whole row, so a table with a large text column is expensive to update even for a small change.

Every index on the table may need updating. A heavily indexed table is slow to write.

So a row updated in a tight loop a million times generates a million dead versions, and the table grows until VACUUM catches up. That is called bloat, and it is the most common PostgreSQL performance surprise.

Check your work

Why copies = copies + 1 beats read-then-write. The database does the arithmetic on the current value.

The three habits. WHERE in the same breath, SELECT first, and BEGIN so you can ROLLBACK.

What the row count is. Your confirmation dialogue.

What RETURNING gives you on a write. Exactly which rows changed, in the same statement.

How to archive and delete atomically. WITH ... DELETE ... RETURNING feeding an INSERT.

The UPDATE ... FROM warning. Several matching rows means one is picked arbitrarily, with no error.

When to soft delete. When the record might be asked about later.

The better option than either. A status column, so history is preserved.

Three things about TRUNCATE. No WHERE, transactional in PostgreSQL, and CASCADE reaches every referencing table.

What UPDATE 0 usually means. A bug — and it is the basis of compare-and-swap.

Why an UPDATE is not free. It writes a new row version and updates the indexes; dead versions are bloat until VACUUM.

Practice

Update one book's copies by id. Then with copies + 1.
Write a DELETE with no WHERE — inside BEGIN — read the row count, and ROLLBACK.
Take a DELETE you intend to run, write it as a SELECT first, then change the verb.
Use RETURNING * on an UPDATE and on a DELETE.
Archive and delete old loans in one statement with WITH ... RETURNING.
Create a deliveries table and apply it to books with UPDATE ... FROM.
Put two rows for the same book in deliveries and run it again. Check which one was applied.
Fix that by aggregating in a subquery.
Add deleted_at to members, soft-delete one, then write a query that forgets the WHERE deleted_at IS NULL and note what it returns.
TRUNCATE a test table inside a transaction and roll it back.
Try TRUNCATE members and read the error about foreign keys. Then use CASCADE on a copy and count what it removed.
Run an UPDATE whose WHERE matches nothing. Note the row count and describe when that would be a bug.
Update the same row 10,000 times, then look at the table size with pg_total_relation_size before and after VACUUM.

Official documentation

PostgreSQL — UPDATE — Including FROM and the warning about multiple matching rows.
PostgreSQL — DELETE — And USING, the delete equivalent of UPDATE ... FROM.
PostgreSQL — TRUNCATE — Including RESTART IDENTITY and CASCADE.
PostgreSQL — Routine vacuuming — Why updates create dead rows, and what bloat is.

Next: transactions.

UPDATE

DELETE