CTE with INSERT, UPDATE, DELETE (writable CTEs) in PostgreSQL - Step-by-Step Execution

Concept Flow - CTE with INSERT, UPDATE, DELETE (writable CTEs)

Start Query

↓

Define CTE

↓

Execute INSERT/UPDATE/DELETE

↓

CTE returns affected rows

↓

Main Query uses CTE result

↓

Return final output

The query starts by defining a CTE that performs an INSERT, UPDATE, or DELETE. The CTE returns the affected rows, which the main query can then use to produce the final output.

Execution Sample

PostgreSQL

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

This query increases salaries by 10% for Sales department employees and returns their ids and new salaries.

Execution Table

Step	Action	Rows Affected	CTE Output	Main Query Output
1	Start query execution	0	None	None
2	Update employees where department='Sales'	3	[{id:1, salary:55000}, {id:3, salary:66000}, {id:5, salary:60500}]	None
3	Select all from updated CTE	0	Same as above	[{id:1, salary:55000}, {id:3, salary:66000}, {id:5, salary:60500}]
4	Query ends	0	Final CTE output	Final main query output

💡 All matching rows updated; CTE returns updated rows; main query outputs these rows.

Variable Tracker

Variable	Start	After Step 2	After Step 3	Final
updated	None	[{id:1, salary:55000}, {id:3, salary:66000}, {id:5, salary:60500}]	[{id:1, salary:55000}, {id:3, salary:66000}, {id:5, salary:60500}]	[{id:1, salary:55000}, {id:3, salary:66000}, {id:5, salary:60500}]

Key Moments - 3 Insights

Why does the CTE return rows after an UPDATE?

Because the CTE uses RETURNING clause, it outputs the updated rows, which the main query can then select, as shown in execution_table step 2 and 3.

Can the main query use the CTE output for further filtering?

Yes, the main query can use the rows returned by the writable CTE for filtering or joining, as the CTE acts like a temporary result set (see execution_table step 3).

What happens if no rows match the UPDATE condition?

The CTE returns an empty set, so the main query will output no rows. This is implied by the exit_note and variable_tracker showing empty or no changes.

Visual Quiz - 3 Questions

Look at the execution_table, how many rows were updated in step 2?

A 5

B 0

C 3

D 1

💡 Hint

Check the 'Rows Affected' column in execution_table row with Step 2.

At which step does the main query output the updated rows?

A Step 1

B Step 3

C Step 2

D Step 4

💡 Hint

Look at the 'Main Query Output' column in execution_table.

If the WHERE condition matched no rows, what would the CTE output be?

A Empty set

B All rows in employees table

C Error

D Only one row

💡 Hint

Refer to key_moments explanation about no matching rows.

Concept Snapshot

Writable CTEs allow INSERT, UPDATE, DELETE inside WITH clause.
Use RETURNING to get affected rows.
Main query can select from CTE result.
Useful for chaining data changes and queries.
Example: WITH cte AS (UPDATE ... RETURNING ...) SELECT * FROM cte;

Full Transcript

This visual execution trace shows how writable CTEs work in PostgreSQL. The query starts by defining a CTE that performs an UPDATE on the employees table, increasing salaries for the Sales department. The CTE uses RETURNING to output the updated rows. The main query then selects all rows from this CTE. The execution table tracks each step: starting the query, performing the update affecting 3 rows, selecting from the CTE, and ending the query. The variable tracker shows the 'updated' variable holding the updated rows after step 2 and 3. Key moments clarify why the CTE returns rows, how the main query uses them, and what happens if no rows match. The quiz tests understanding of rows affected, output timing, and empty results. The snapshot summarizes syntax and behavior for quick reference.