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
15. Display values in UPDATE
Summary

Instruction

Good job! As you probably guessed, OUTPUT is also used in UPDATE queries. It's quite similar to how it's used in DELETE or INSERT statements.

The teacher entered the wrong subject for the student Alan Stanley, Id = 10. Let's take a look:

UPDATE Exam
SET Subject = N'Italian'
OUTPUT Deleted.Subject AS SubjectBefore, 
Inserted.Subject AS SubjectAfter
WHERE StudentId = 10;

This statement changes the exam subject from Spanish to Italian. Notice that the OUTPUT clause is placed after SET. There are also two prefixes in an UPDATE query: Inserted and Deleted.

These two prefixes allow us to see the record data both before and after the update. The Deleted prefix is used for the original data, and the Inserted prefix is used for the updated data. Each output column gets an added alias (SubjectBefore, SubjectAfter).

The result table is:

SubjectBefore SubjectAfter
Spanish Italian

Exercise

The student Angela Smith (Id = 2) married Peter Morgan. The teacher has to update Angela's last name to Morgan. Update the data, and display Angela's last name (column OldLastName) and new last name (column NewLastName).