Introduction
Inserting and updating NULLs
Conditions in UPDATE and DELETE
Using values from another column
11. Update using a value from another column
Return rows in INSERT, UPDATE, DELETE
Summary

Instruction

Good job! So far, we've always inserted fixed values when updating data. However, when we update we can also refer to values from another column.

Suppose someone has found mistakes made in the scoring of written and oral English exams. Say that each written English exam score needs four points added to it and each oral English exam score is actually six points less than the written exam score. Here's the statement to correct this mistake:

UPDATE Exam
SET WrittenExamScore = WrittenExamScore + 4,
OralExamScore = WrittenExamScore - 6
WHERE Subject = N'English'

With the SET command, we assigned to the WrittenExamScore column its current value plus 4 points. Similarly, we updated the OralExamScore column, only we subtracted six points from the written exam score (we took the value from WrittenExamScore). In the WHERE clause, we limited this UPDATE operation to exams for English.

Exercise

The oral exams with no date assigned take place four days after the WrittenExamDate. Update the data.

Stuck? Here's a hint!

To add four days to WrittenExamDate, use:

DATEADD(day, 4, WrittenExamDate)