Kickstart 2020 with new opportunities! - 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
13. 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
  written_exam_score = written_exam_score + 4,
  oral_exam_score = written_exam_score - 6
WHERE subject = 'English'

With the SET command, we assigned to the written_exam_score column its current value plus 4 points. Similarly, we updated the oral_exam_score column, only we subtracted six points from the written exam score (we took the value from written_exam_score). 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 written_exam_date. Update the data.

Stuck? Here's a hint!

To add four days to written_exam_date, use:

written_exam_date + 4