SQL UPDATE SET WHERE — Modifying Existing Rows

Updating Data Safely

The UPDATE statement modifies existing rows. The most critical rule: always include a WHERE clause unless you intentionally want to update every row in the table.

Basic Update

UPDATE employees
SET salary = 85000
WHERE id = 42;

Multi-Column Update

UPDATE employees
SET salary = salary * 1.10,
    updated_at = CURRENT_TIMESTAMP
WHERE department_id = 3;

Update with Subquery

UPDATE employees
SET department_id = (
  SELECT id FROM departments WHERE name = 'Engineering'
)
WHERE name = 'Alice Smith';

Update with JOIN

PostgreSQL uses FROM:

UPDATE employees e
SET salary = salary * 1.10
FROM departments d
WHERE e.department_id = d.id
  AND d.name = 'Engineering'
RETURNING e.name, e.salary;

MySQL uses JOIN:

UPDATE employees e
JOIN departments d ON e.department_id = d.id
SET e.salary = e.salary * 1.10
WHERE d.name = 'Engineering';

Safety Tips

1. Always test with SELECT first: Replace UPDATE ... SET ... with SELECT * using the same WHERE clause to preview affected rows.
2. Run inside a transaction: BEGIN; UPDATE ...; SELECT ...; COMMIT; (or ROLLBACK if wrong).
3. Limit scope: Use specific WHERE conditions rather than broad ones.