UPDATE & DELETE

UPDATE

UPDATE students SET cgpa = 3.75 WHERE roll_no = 'CSE-101';

UPDATE students
SET department = 'SWE', updated_at = now()
WHERE department = 'CSE' AND admitted_on >= '2024-01-01';

UPDATE students SET cgpa = cgpa + 0.10 WHERE cgpa < 3.0;

-- several columns at once
UPDATE students SET (name_en, name_bn) = ('Rahim U.', 'রহিম উ.') WHERE id = 1;

-- conditional
UPDATE students
SET grade = CASE
  WHEN cgpa >= 3.75 THEN 'A+'
  WHEN cgpa >= 3.50 THEN 'A'
  WHEN cgpa >= 3.00 THEN 'B'
  ELSE 'C'
END;

UPDATE … FROM

Postgres uses FROM rather than a join in the UPDATE clause:

UPDATE students s
SET dept_name = d.name_en
FROM departments d
WHERE s.department = d.code AND d.active;

You can also update from a VALUES list, which is the efficient way to apply many different values in one statement:

UPDATE students s
SET cgpa = v.cgpa
FROM (VALUES (1, 3.90), (2, 3.10), (3, 3.55)) AS v(id, cgpa)
WHERE s.id = v.id;

DELETE

DELETE FROM students WHERE roll_no = 'CSE-104';
DELETE FROM students WHERE cgpa < 2.0 AND admitted_on < '2020-01-01' RETURNING id;

-- delete using another table
DELETE FROM enrollments e
USING students s
WHERE e.student_id = s.id AND s.status = 'withdrawn';

-- delete orphans
DELETE FROM enrollments e
WHERE NOT EXISTS (SELECT 1 FROM students s WHERE s.id = e.student_id);

Deleting in batches

A single huge DELETE holds locks and bloats the table. Loop over chunks instead:

DELETE FROM logs
WHERE id IN (
  SELECT id FROM logs WHERE created_at < now() - interval '90 days' LIMIT 10000
);

Repeat until zero rows are affected. For time-series data, partitioning and DROP PARTITION is far better than deleting at all.

Safety habits

1. Preview. Run the same WHERE as a SELECT first.
2. Use a transaction — and remember Postgres rolls back DDL too:

BEGIN;
  UPDATE students SET cgpa = 0 WHERE department = 'CSE';
  SELECT count(*) FROM students WHERE cgpa = 0;   -- check before deciding
ROLLBACK;   -- or COMMIT;

3. Use RETURNING to see exactly what changed.
4. In psql, \set AUTOCOMMIT off makes every statement require an explicit COMMIT — a good safety net on production.

MVCC and bloat

An UPDATE in Postgres does not overwrite a row: it writes a new version and marks the old one dead. DELETE only marks rows dead. The space is reclaimed later by autovacuum. That means a large update or delete temporarily grows the table, and very frequent updates on the same rows cause bloat. Keep transactions short so vacuum can clean up, and watch:

SELECT relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;