End of Summer - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Inserting and updating NULLs
Conditions in UPDATE and DELETE
Updating the list of columns in one query
Using values from another column
Return rows in INSERT, UPDATE, DELETE
15. Display deleted values
Summary

Instruction

Fantastic! Time for another chunk of knowledge :)

So far, we've deleted records from a table without seeing any of the deleted values. Sometimes, though, we would like to know details about what we're deleting. For this, PostgreSQL gives us the RETURNING clause, which displays the column values of deleted rows. It looks like this:

DELETE FROM student
WHERE last_name IS NULL
RETURNING *;

This returns a table with all the removed records – in this case, the records for any students without a last name.

The new element in this DELETE statement is the RETURNING clause, which you put at the end of the query. After the RETURNING clause, we put an asterisk (*) to denote that we wanted data from all columns. We could also list certain column names, like this:

DELETE FROM student
WHERE last_name IS NULL
RETURNING id, first_name;

This allows you to see the ID and first name values for all deleted records.

Exercise

Remove all exam results for which the sum of scores from both exams (written_exam_score and oral_exam_score) is greater than 40. Show only the exam ID and the student ID.