UPDATE

UPDATE is how you modify existing data in a table. Data changes all the time — people move, products go out of stock, statuses change. UPDATE lets you keep your database current.

Basic Update

The syntax is straightforward — specify the table, the columns to change, and a WHERE clause to target the right rows:

UPDATE students
SET email = 'alice.new@example.com'
WHERE id = 1;

This changes the email for the student with id 1. Without the WHERE clause, every row in the table would be updated. Be careful with that.

Updating Multiple Columns

You can change several columns at once:

UPDATE students
SET first_name = 'Alicia',
    last_name = 'Smith-Johnson',
    email = 'alicia@example.com'
WHERE id = 1;

Just separate the column-value pairs with commas.

Using Expressions

You can use expressions in SET clauses:

-- Increment a counter
UPDATE products
SET stock = stock - 1
WHERE id = 5;

-- Calculate a new value
UPDATE orders
SET total_price = quantity * unit_price
WHERE order_id = 100;

This is very powerful — you can update based on the current value without knowing what it is in advance.

Conditional Updates

Use CASE for conditional logic within an UPDATE:

UPDATE students
SET status = CASE
  WHEN enrollment_date < '2020-01-01' THEN 'senior'
  WHEN enrollment_date < '2023-01-01' THEN 'junior'
  ELSE 'freshman'
END;

This updates different rows with different values based on a condition, all in one query.

Returning Updated Rows

Just like INSERT, you can get back the updated data:

UPDATE students
SET email = 'updated@example.com'
WHERE id = 1
RETURNING id, first_name, email;

This shows you exactly what changed. Very useful for confirming updates in application code.

Updating from Another Table

Sometimes you need to update based on data in another table. PostgreSQL supports this with a FROM clause:

UPDATE students
SET department_name = departments.name
FROM departments
WHERE students.department_id = departments.id;

This copies the department name from the departments table into the students table.

Common Mistakes

The most dangerous mistake with UPDATE is forgetting the WHERE clause:

-- This updates EVERY row in the table!
UPDATE students SET email = 'wrong@example.com';

Always double-check your WHERE clause before running an UPDATE. If you are unsure, run a SELECT with the same WHERE clause first to see which rows will be affected.