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.

Practice

1. What is the main purpose of using writable CTEs in PostgreSQL?

easy

A. To speed up SELECT queries by caching results

B. To create temporary tables that last beyond the current transaction

C. To perform INSERT, UPDATE, or DELETE operations inside a WITH clause

D. To define functions that return multiple rows

Solution

1. Step 1: Understand writable CTEs

   Writable CTEs allow data modification commands like INSERT, UPDATE, or DELETE inside a WITH clause.

2. Step 2: Compare options

   Options B, C, and D describe other features unrelated to writable CTEs.

3. Final Answer:

   To perform INSERT, UPDATE, or DELETE operations inside a WITH clause -> Option C

4. Quick Check:

   Writable CTEs = Data modification in WITH [OK]

Hint: Writable CTEs = data changes inside WITH clause [OK]

Common Mistakes:

• Confusing writable CTEs with temporary tables
• Thinking writable CTEs speed up SELECT queries
• Mixing writable CTEs with function definitions

2. Which of the following is the correct syntax to update rows using a writable CTE in PostgreSQL?

easy

A. UPDATE employees WITH (salary = salary * 1.1) RETURNING *;

B. WITH updated AS (UPDATE employees SET salary = salary * 1.1) RETURNING *;

C. WITH updated AS (SELECT * FROM employees WHERE salary > 5000) UPDATE updated SET salary = salary * 1.1;

D. WITH updated AS (UPDATE employees SET salary = salary * 1.1 RETURNING *) SELECT * FROM updated;

Solution

1. Step 1: Review writable CTE update syntax

   The correct syntax uses WITH updated AS (UPDATE ... RETURNING *) followed by a SELECT from updated.

2. Step 2: Analyze options

   WITH updated AS (UPDATE employees SET salary = salary * 1.1 RETURNING *) SELECT * FROM updated; matches the correct syntax. Options A, C, and D have syntax errors or misuse RETURNING.

3. Final Answer:

   WITH updated AS (UPDATE employees SET salary = salary * 1.1 RETURNING *) SELECT * FROM updated; -> Option D

4. Quick Check:

   Writable CTE update = WITH + UPDATE + RETURNING + SELECT [OK]

Hint: Use WITH (UPDATE ... RETURNING *) then SELECT [OK]

Common Mistakes:

• Omitting RETURNING clause in writable CTE
• Trying to UPDATE inside WITH without SELECT
• Misplacing RETURNING outside the CTE

3. Given the table products(id, name, stock) with rows: (1, 'Pen', 10), (2, 'Pencil', 5), what will be the result of this query?

WITH updated AS (UPDATE products SET stock = stock + 10 WHERE name = 'Pen' RETURNING *) SELECT * FROM updated;

medium

A. [{id:1, name:'Pen', stock:20}]

B. [{id:1, name:'Pen', stock:10}]

C. [{id:2, name:'Pencil', stock:15}]

D. Empty result set

Solution

1. Step 1: Identify updated rows

   The UPDATE increases stock by 10 only for product with name 'Pen' (id=1), changing stock from 10 to 20.

2. Step 2: Understand RETURNING and SELECT

   The RETURNING * returns the updated row, and the SELECT outputs it.

3. Final Answer:

   [{id:1, name:'Pen', stock:20}] -> Option A

4. Quick Check:

   Updated Pen stock = 10 + 10 = 20 [OK]

Hint: RETURNING shows updated rows immediately [OK]

Common Mistakes:

• Assuming all rows are updated
• Ignoring RETURNING clause output
• Confusing stock before and after update

4. Identify why this writable CTE query returns an empty result set even if matching rows exist:

WITH deleted AS (DELETE FROM orders WHERE status = 'cancelled') SELECT * FROM deleted;

medium

A. The WHERE clause syntax is incorrect

B. Missing RETURNING clause in DELETE statement

C. SELECT * FROM deleted should be SELECT COUNT(*)

D. DELETE cannot be used inside WITH clause

Solution

1. Step 1: Check DELETE in writable CTE

   DELETE inside WITH must include RETURNING to produce rows for the outer SELECT.

2. Step 2: Analyze the query

   The query lacks RETURNING, so 'deleted' has no rows to select from, resulting in an empty result set.

3. Final Answer:

   Missing RETURNING clause in DELETE statement -> Option B

4. Quick Check:

   Writable CTE DELETE needs RETURNING [OK]

Hint: Always add RETURNING with DELETE in writable CTEs [OK]

Common Mistakes:

• Omitting RETURNING after DELETE in CTE
• Thinking DELETE can't be in WITH clause
• Misunderstanding SELECT after writable CTE

5. You want to insert new users and immediately update their status to 'active' using writable CTEs. Which query correctly does this in one statement?

hard

A. WITH ins AS (INSERT INTO users(name) VALUES ('Alice'), ('Bob') RETURNING id, name) UPDATE users SET status = 'active' FROM ins WHERE users.id = ins.id RETURNING users.*;

B. INSERT INTO users(name) VALUES ('Alice'), ('Bob'); WITH upd AS (UPDATE users SET status = 'active' WHERE name IN ('Alice', 'Bob') RETURNING *) SELECT * FROM upd;

C. WITH ins AS (INSERT INTO users(name) VALUES ('Alice'), ('Bob')) UPDATE users SET status = 'active' WHERE id IN (SELECT id FROM ins);

D. WITH ins AS (INSERT INTO users(name) VALUES ('Alice'), ('Bob')) UPDATE ins SET status = 'active' WHERE name IN ('Alice', 'Bob');

Solution

1. Step 1: Insert and capture new rows

   Use a writable CTE to insert and RETURNING id, name to identify new users.

2. Step 2: Update using CTE results

   Update users joining with inserted rows on id, setting status to 'active', and RETURNING updated rows.

3. Step 3: Analyze options

   WITH ins AS (INSERT INTO users(name) VALUES ('Alice'), ('Bob') RETURNING id, name) UPDATE users SET status = 'active' FROM ins WHERE users.id = ins.id RETURNING users.*; correctly chains insert and update in one statement with proper RETURNING and join. INSERT INTO users(name) VALUES ('Alice'), ('Bob'); WITH upd AS (UPDATE users SET status = 'active' WHERE name IN ('Alice', 'Bob') RETURNING *) SELECT * FROM upd; separates insert and update into multiple statements. WITH ins AS (INSERT INTO users(name) VALUES ('Alice'), ('Bob')) UPDATE users SET status = 'active' WHERE id IN (SELECT id FROM ins); lacks RETURNING so CTE returns no rows and subquery updates nothing. WITH ins AS (INSERT INTO users(name) VALUES ('Alice'), ('Bob')) UPDATE ins SET status = 'active' WHERE name IN ('Alice', 'Bob'); incorrectly attempts to UPDATE the CTE which is invalid.

4. Final Answer:

   WITH ins AS (INSERT INTO users(name) VALUES ('Alice'), ('Bob') RETURNING id, name) UPDATE users SET status = 'active' FROM ins WHERE users.id = ins.id RETURNING users.*; -> Option A

5. Quick Check:

   Writable CTE insert + update with join = WITH ins AS (INSERT INTO users(name) VALUES ('Alice'), ('Bob') RETURNING id, name) UPDATE users SET status = 'active' FROM ins WHERE users.id = ins.id RETURNING users.*; [OK]

Hint: Chain INSERT and UPDATE with RETURNING and FROM join [OK]

Common Mistakes:

• Omitting RETURNING clause before using CTE in subquery
• Splitting insert and update into separate statements
• Omitting RETURNING to get updated rows