Back-To-School Deals - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Inserting and updating NULLs
Conditions in UPDATE and DELETE
Using values from another column
Return rows in INSERT, UPDATE, DELETE
13. Display deleted values
Summary

Instruction

Fantastic! Time for another chunk of knowledge :)

So far, we've deleted data from a table without seeing details about the deleted records. Sometimes, though, we would like to know in detail what we're deleting. SQL Server gives us the OUTPUT clause, which displays the values of columns in deleted rows.

DELETE FROM Student
OUTPUT Deleted.*
WHERE LastName IS NULL;

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

The new element in this DELETE statement is the OUTPUT clause, you put it after the table name. In the OUTPUT clause, you can use the prefix Deleted to refer to the rows deleted by this statement. Notice that the Deleted prefix is separated by a dot from the asterisk character (*). The asterisk denotes all columns. Of course, in its place, you could list chosen column names, like this:

DELETE FROM Student
OUTPUT Deleted.LastName, Deleted.FirstName
WHERE LastName IS NULL;

This gives you the chance to see deleted records.

Exercise

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