Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

How to Insert, Update, or Delete Data in MS SQL Server

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).