Warming up the neural circuits...
UPDATE changes values in rows that already exist. It's powerful — and dangerous. By the end of this module you will:
What it is: UPDATE is a DML command that modifies existing rows in a table. You specify which columns to change (SET) and which rows to affect (WHERE). Without WHERE, all rows are updated.
Why we use it: Data changes over time — users update their profiles, prices change, statuses get updated. UPDATE is how you reflect these changes in your database.
When we use it: When editing user profiles, updating order statuses, changing prices, marking records as deleted, or any time existing data needs to change.
-- Update a single user's email
UPDATE users
SET email = 'alice.new@example.com'
WHERE id = 1;What it is: Without a WHERE clause, UPDATE affects every row in the table. This is almost always a mistake — in production, it can corrupt thousands of records in an instant.
Why we use it: This section exists to warn you. In 10+ years of database work, forgetting WHERE is the #1 cause of data loss incidents. Always write WHERE first, then the of the UPDATE.
When we use it: Never intentionally. This is a mistake to avoid.
-- DANGER: Every user's email is now the same!
UPDATE users SET email = 'reset@example.com';Pro Rule: Never write an UPDATE statement without writing the WHERE clause first. Always test with SELECT first.
What it is: You can update multiple columns in a single UPDATE statement by separating them with commas in the SET clause. All changes are applied atomically — either all succeed or all fail.
Why we use it: When a single event changes multiple fields — a user updates their profile (name + email + phone), or an order changes status (status + updated_at + notes).
When we use it: When multiple related fields need to change together, to reduce the number of database round-trips, or when atomicity is important.
-- Update multiple columns at once
UPDATE users
SET age = 26, email = 'alice.updated@example.com'
WHERE id = 1;What it is: Instead of setting a column to a literal value, you can use expressions — arithmetic, function calls, or references to other columns. The expression is evaluated for each row being updated.
Why we use it: Many updates are relative, not absolute — "increase price by 10%", "set updated_at to now", "increment counter by 1". Expressions handle these without needing to know the current value.
When we use it: When applying bulk changes (price increases), setting timestamps, computing derived values, or applying transformations to existing data.
-- Increase all prices by 10%
UPDATE products
SET price = price * 1.10
WHERE category = 'Electronics';
-- Set a timestamp
UPDATE posts
SET published = true, published_at = NOW()
WHERE id = 5;What it is: RETURNING (PostgreSQL extension) returns the updated rows immediately after the UPDATE executes. It's like combining UPDATE and SELECT in one atomic operation.
Why we use it: After updating a record, you often need to return the updated data to the client (e.g., in a REST ). Without RETURNING, you'd need two queries: UPDATE then SELECT.
When we use it: In REST APIs after updating resources, when you need to verify what changed, or when building real-time applications that need immediate feedback.
UPDATE users
SET role = 'admin'
WHERE id = 1
RETURNING id, username, role;What it is: The safe UPDATE pattern is a workflow: always test your WHERE clause with SELECT first, verify the rows that will be affected, then run the UPDATE with the same WHERE clause.
Why we use it: This pattern prevents accidental mass updates. By previewing which rows will be affected, you can catch mistakes before they cause data loss.
When we use it: Every time you run an UPDATE in production. It takes 5 seconds and can save hours of recovery work.
-- Step 1: Test with SELECT
SELECT * FROM users WHERE age < 18;
-- Step 2: Verify the rows you want to update
-- Step 3: Run the UPDATE with the same WHERE clause
UPDATE users SET role = 'minor' WHERE age < 18;Using your courses table:
is_published status for all courses where price > 2000Next up: The other dangerous operation — DELETE. In the next module, you'll learn how to remove data safely.