Updating data using subqueries
Observe the two tables
courses below. The
students is a reference to
id in the
courses table, to hold the information related to the course the student is enrolled in.
Let’s say we want to add a column
courseName to the
students table. This column should store the actual name of the
courseId is referencing to.
To do so, we need to
UPDATE each row of record with a specific data relevant to that row (course information). This can only be achieved by a subquery that gets the
name of the course for
courseId in each row. Observe the subquery in the
SET clause in
UPDATE statement below.
Notice two things about the above
- The value to be set in UPDATE clause for a column should be a single value (not a list), hence the
LIMIT1 in the subquery
- The subquery is a correlated subquery, as it utilizes the
studentstable in the parent statement