UPDATE and DELETE — Modifying and Removing Rows

UPDATE and DELETE are powerful and dangerous operations — a missing WHERE clause updates or deletes every row in the table. This mistake has caused production outages at companies large and small. Beyond safety, understanding how to use UPDATE ... RETURNING to get modified rows back, how to update with subqueries, and how to implement soft delete (setting a deleted_at timestamp instead of removing the row) gives you the SQL tools needed for all FastAPI CRUD operations.

UPDATE #

-- Update specific columns for specific rows (WHERE is essential!)
UPDATE users
SET role = 'editor'
WHERE id = 3;

-- Update multiple columns at once
UPDATE posts
SET
    title      = 'Updated Title',
    status     = 'published',
    updated_at = NOW()
WHERE id = 42;

-- Update with an expression
UPDATE posts
SET view_count = view_count + 1
WHERE id = 42;

-- Update based on another column's value
UPDATE users
SET is_active = FALSE
WHERE last_login_at < NOW() - INTERVAL '1 year';

-- Update multiple rows matching a condition
UPDATE posts
SET status = 'archived'
WHERE created_at < NOW() - INTERVAL '2 years'
  AND status = 'draft';

UPDATE … RETURNING #

-- Get the updated row back in one query
UPDATE posts
SET
    title      = 'New Title',
    updated_at = NOW()
WHERE id = 42
RETURNING id, title, updated_at;

-- Verify the update was successful (returns empty if no rows matched)
UPDATE users
SET is_active = FALSE
WHERE id = 999
RETURNING id;
-- If no rows returned: user_id 999 did not exist

-- Return multiple updated rows
UPDATE posts
SET status = 'published', published_at = NOW()
WHERE author_id = 1 AND status = 'draft'
RETURNING id, title, published_at;

DELETE #

-- Delete a specific row
DELETE FROM comments WHERE id = 15;

-- Delete with a condition
DELETE FROM posts WHERE status = 'draft' AND created_at < NOW() - INTERVAL '30 days';

-- Delete and return the deleted row(s)
DELETE FROM users WHERE id = 3
RETURNING id, email, name;
-- Confirms which row was deleted

-- Delete all rows in a table (faster than DELETE with no WHERE)
-- TRUNCATE removes all rows without scanning them — much faster for large tables
TRUNCATE TABLE sessions;

-- TRUNCATE with cascade (also truncates tables with FK references)
TRUNCATE TABLE users CASCADE;   -- also truncates posts, comments, etc.
-- WARNING: irreversible! Use only in development/test environments

Soft Delete Pattern #

-- Instead of deleting rows, mark them as deleted (preserves audit trail)
-- Requires a deleted_at column (TIMESTAMPTZ, nullable)

-- Soft delete a post
UPDATE posts
SET deleted_at = NOW()
WHERE id = 42
RETURNING id, deleted_at;

-- Query active (not deleted) posts
SELECT id, title, status
FROM posts
WHERE deleted_at IS NULL   -- only non-deleted rows
  AND status = 'published'
ORDER BY created_at DESC;

-- Restore a soft-deleted post
UPDATE posts
SET deleted_at = NULL
WHERE id = 42;

-- Permanent hard delete (clean up old soft-deleted rows)
DELETE FROM posts
WHERE deleted_at < NOW() - INTERVAL '90 days';

-- Advantages of soft delete:
-- Recoverable: accidental deletion can be undone
-- Audit trail: know when and (with user_id) who deleted
-- Referential: child rows (comments) stay intact, queries can include them
-- 
-- Disadvantages:
-- Every query needs WHERE deleted_at IS NULL
-- Table grows without regular cleanup
-- Unique constraints need adjustment (unique email among active users)

Common Mistakes #

Mistake 1 — UPDATE or DELETE without WHERE #

❌ Wrong — modifies or deletes every row:

UPDATE users SET is_active = FALSE;   -- bans ALL users!
DELETE FROM posts;                     -- deletes ALL posts!

✅ Correct — always include WHERE:

UPDATE users SET is_active = FALSE WHERE id = 42;   -- ✓ specific row
DELETE FROM posts WHERE id = 42;                    -- ✓ specific row

Mistake 2 — Not using RETURNING to verify affected rows #

❌ Wrong — cannot tell if the row existed:

UPDATE posts SET title = 'X' WHERE id = 9999;
-- Affected 0 rows silently — was post 9999 not found? Typo?

✅ Correct — RETURNING reveals if the row was found:

UPDATE posts SET title = 'X' WHERE id = 9999
RETURNING id;
-- Empty result set → post 9999 doesn't exist → return 404 in FastAPI ✓

Mistake 3 — Using TRUNCATE in production when DELETE is safer #

❌ Wrong — TRUNCATE cannot be filtered and may cascade unexpectedly:

TRUNCATE TABLE users;   -- removes ALL users, cannot add WHERE clause!

✅ Correct — use DELETE with a WHERE clause for selective removal:

DELETE FROM users WHERE created_at > '2025-01-01' AND is_active = FALSE;   -- ✓

Quick Reference #