CTE with INSERT, UPDATE, DELETE (writable CTEs) in PostgreSQL

Introduction

Writable CTEs let you change data (insert, update, delete) inside a query. This helps organize complex changes step-by-step.

You want to update some rows and then use those updated rows immediately in the same query.

You need to insert new data and then select from it right away.

You want to delete some rows and then see what remains or do more changes.

You want to keep your data changes clear and easy to read by breaking them into parts.

You want to perform multiple data changes in one query safely.

Syntax

PostgreSQL

WITH cte_name AS (
  INSERT INTO table_name (column_list)
  VALUES (value_list)
  RETURNING *
)
SELECT * FROM cte_name;

Writable CTEs use INSERT, UPDATE, or DELETE inside the WITH clause.

Use RETURNING to get the changed rows and use them in the next query.

Examples

This updates salaries in Sales and shows the updated rows.

PostgreSQL

WITH updated AS (
  UPDATE employees
  SET salary = salary * 1.1
  WHERE department = 'Sales'
  RETURNING *
)
SELECT * FROM updated;

This inserts a new product and returns its details.

PostgreSQL

WITH inserted AS (
  INSERT INTO products (name, price)
  VALUES ('New Product', 20)
  RETURNING *
)
SELECT * FROM inserted;

This deletes old orders and counts how many were deleted.

PostgreSQL

WITH deleted AS (
  DELETE FROM orders
  WHERE order_date < '2023-01-01'
  RETURNING *
)
SELECT COUNT(*) FROM deleted;

Sample Program

This query adds 500 to the salary of all employees in the HR department and then shows their id, name, and new salary.

PostgreSQL

WITH updated_employees AS (
  UPDATE employees
  SET salary = salary + 500
  WHERE department = 'HR'
  RETURNING id, name, salary
)
SELECT * FROM updated_employees ORDER BY id;

Important Notes

Writable CTEs run the data change first, then the following SELECT uses the changed data.

Always use RETURNING to get the affected rows; otherwise, you can't use the changed data in the next step.

Writable CTEs help keep queries clean and easy to understand when doing multiple steps.

Summary

Writable CTEs let you insert, update, or delete data inside a WITH clause.

Use RETURNING to get the changed rows and use them immediately.

This makes complex data changes easier to write and read.