Oracle SQL*Plus Pocket Reference, 3rd Edition

Updating Data

Use the UPDATE statement to modify column values in existing table rows.

Simple Updates

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.

Noncorrelated Subqueries in the SET Clause

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.

Correlated Subqueries in the SET Clause

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 ...