UPDATE with RETURNING clause in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the RETURNING clause do in an UPDATE statement?

It returns the updated rows immediately after the UPDATE operation, showing the new values without needing a separate SELECT query.

beginner

Write a simple UPDATE statement with RETURNING to change a user's email and get the updated email back.

UPDATE users SET email = '[e-mail]' WHERE id = 1 RETURNING email;

intermediate

Why is using RETURNING in UPDATE useful compared to running UPDATE then SELECT?

It saves time and resources by combining update and retrieval in one step, reducing the number of queries and network traffic.

intermediate

Can RETURNING clause return multiple columns? Give an example.

Yes, it can return multiple columns. Example: UPDATE products SET price = price * 1.1 RETURNING id, price;

beginner

What happens if no rows match the UPDATE condition when using RETURNING?

No rows are updated and the RETURNING clause returns an empty result set.

What is the purpose of the RETURNING clause in an UPDATE statement?

A To rollback the update if a condition fails

B To delete rows after updating

C To return the updated rows immediately after the update

D To create a backup of updated rows

The RETURNING clause returns the updated rows right after the UPDATE operation.

Which of the following is a valid use of RETURNING in PostgreSQL?

A UPDATE users SET name = 'Alice' WHERE id = 1;

B UPDATE users RETURNING name WHERE id = 1;

C RETURNING UPDATE users SET name = 'Alice';

D UPDATE users SET name = 'Alice' RETURNING id, name;

Option D correctly places RETURNING after the SET clause to return specified columns.

If an UPDATE statement with RETURNING affects no rows, what is returned?

A An error message

B An empty result set

C All rows in the table

D The previous values before update

No rows updated means RETURNING returns an empty result set.

Can RETURNING be used to get the old values before update?

A No, RETURNING only returns new values

B Yes, by using RETURNING * BEFORE UPDATE

C Yes, by specifying OLD.column_name

D No, you must use a trigger for old values

RETURNING returns the new values after update; old values require triggers or other methods.

Which SQL dialect supports UPDATE with RETURNING clause?

A PostgreSQL

B MySQL (all versions)

C SQLite

D Oracle

PostgreSQL supports UPDATE with RETURNING; MySQL and SQLite do not support it natively.

Explain how the RETURNING clause works in an UPDATE statement and why it is useful.

Think about how you get updated data without a second query.

Write an example UPDATE query using RETURNING to update a record and get back multiple columns.

Use RETURNING after SET and list columns separated by commas.