Deals Of The Week - 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
12. Update a list of columns using a subquery
Using values from another column
Return rows in INSERT, UPDATE, DELETE
Summary

Instruction

Great! You now know a shorter way to update a list of columns. But you can also update columns using a subquery instead of a list of values. Check out this query:

UPDATE exam
SET
  (oral_exam_date, oral_exam_score, oral_score_date) =
  (SELECT oral_exam_date, oral_exam_score, oral_score_date  
   FROM exam
   WHERE student_id = 5)
WHERE student_id = 4;

This query changes the data for the oral exam of the student whose ID is 4. It uses data from the records of the student with ID of 5 to update student 4's data. Using a subquery is an easy way to correct this kind of mistake.

Exercise

There's another mistake in the university database. The student whose ID is 8 has incorrect first and middle names. They should be the same first and middle names as the student whose ID equals 7. Correct the data using a subquery in the UPDATE statement.