Vol. I · A Postgres Handbook

The Query & the Schema

A reading-room reference for writing PostgreSQL by hand — selects to window functions, with the schema in plain view.

IntermediateINSERT, UPDATE, DELETE

UPDATE with JOIN (UPDATE FROM syntax)

The query

SQL
UPDATE employees e
SET salary = e.salary * 1.15
FROM departments d
WHERE e.dept_id = d.dept_id
  AND d.dept_name = 'Engineering'
  AND e.status = 'active';
Tested against PostgreSQL 16

Note

“PostgreSQL UPDATE FROM syntax joins tables in an UPDATE. More efficient than subquery for multi-table updates.

Tables referenced