UPDATE with RETURNING clause in PostgreSQL - Step-by-Step Execution

Concept Flow - UPDATE with RETURNING clause

Start UPDATE command

↓

Find rows matching WHERE condition

↓

Update specified columns

↓

Collect updated rows

↓

RETURNING clause outputs updated rows

↓

End

The UPDATE command finds rows matching the condition, changes specified columns, then RETURNING outputs the updated rows.

Execution Sample

PostgreSQL

UPDATE employees
SET salary = salary + 500
WHERE department = 'Sales'
RETURNING id, name, salary;

This updates salaries for Sales department employees and returns their id, name, and new salary.

Execution Table

Step	Action	Rows Matched	Columns Updated	RETURNING Output
1	Start UPDATE	-	-	-
2	Find rows WHERE department = 'Sales'	Rows with dept='Sales' found: 2	-	-
3	Update salary = salary + 500	2 rows	salary column updated	-
4	RETURNING id, name, salary	2 rows	-	[{"id": 3, "name": "Alice", "salary": 3500}, {"id": 5, "name": "Bob", "salary": 4200}]
5	End	-	-	-

💡 All matching rows updated and their new values returned by RETURNING clause.

Variable Tracker

Variable	Start	After Step 3	Final
salary of Alice	3000	3500	3500
salary of Bob	3700	4200	4200

Key Moments - 2 Insights

Why do we see output rows after UPDATE when normally UPDATE returns only count?

Because of the RETURNING clause (see execution_table step 4), the updated rows are output instead of just a count.

Does RETURNING show old or new values?

RETURNING shows the new updated values after the change (execution_table step 4 shows updated salaries).

Visual Quiz - 3 Questions

Look at the execution_table at step 2, how many rows matched the WHERE condition?

A 2

B 3

C 0

D 5

💡 Hint

Check the 'Rows Matched' column at step 2 in execution_table.

At which step does the salary column get updated?

A Step 2

B Step 3

C Step 4

D Step 5

💡 Hint

Look at the 'Columns Updated' column in execution_table.

If we remove RETURNING clause, what changes in the output?

A No rows are updated

B Updated rows are still returned

C Only count of updated rows is returned

D Query will fail

💡 Hint

RETURNING clause controls output; without it, UPDATE returns count only.

Concept Snapshot

UPDATE table_name
SET column = value
WHERE condition
RETURNING columns;

- Updates rows matching condition
- RETURNING outputs updated rows
- Useful to see new values immediately

Full Transcript

The UPDATE with RETURNING clause in PostgreSQL first finds rows matching the WHERE condition. Then it updates the specified columns in those rows. After updating, the RETURNING clause outputs the updated rows with the columns requested. This lets you see the new values right away instead of just the count of updated rows. For example, updating salaries in the Sales department and returning id, name, and new salary shows exactly which rows changed and their new data.