IntermediateCTEs & Recursive

CTE in UPDATE statement

The query

SQL

WITH avg_by_dept AS (
  SELECT dept_id, AVG(salary) AS dept_avg
  FROM employees
  GROUP BY dept_id
)
UPDATE employees e
SET salary = e.salary * 1.05
FROM avg_by_dept a
WHERE e.dept_id = a.dept_id
  AND e.salary < a.dept_avg;

Tested against PostgreSQL 16

Note

“CTEs can be used in UPDATE/DELETE/INSERT statements. Very powerful for conditional bulk updates.

Tables referenced

employees10 cols