Use the UPDATE statement to modify column values in existing table rows.
A simple UPDATE statement takes the following form:
UPDATE table_name SET column_name = new_value, column_name = new_value, column_name = new_value, ... WHERE selection_criteria;
For example, the following statement corrects a small problem with a course name; it changes the name to use an “I” (letter) instead of a “1” (digit):
UPDATE course SET course_name = 'Spanish I' WHERE course_name = 'Spanish 1';
Be careful with updates. If you omit the WHERE clause, your update will be applied to all rows in the table.
Rather than specify a new value in the SET clause for a column, you can specify a subquery that returns exactly one value (one column, one row). That value then becomes the new column value. For example:
UPDATE enrollment SET period = ( SELECT period FROM course WHERE course_name = 'English II'), course_name = ( SELECT course_name FROM course WHERE course_name = 'English II'), WHERE course_name = 'English II';
Setting the PERIOD and COURSE_NAME columns to their current values by way of a subquery doesn’t make much sense. I did it only to show that you can use more than one subquery in an UPDATE statement.
Subqueries in UPDATE statements are often more useful when they are correlated. A correlated subquery is one in which the row returned depends on the current row being updated. For example, the following ...