Command Discussion

• The UPDATE clause indicates which table is to be modified.

• The SET clause is followed by the column or columns to be modified. The expression represents the new value to be assigned to the column. The expression can contain constants, column names, or arithmetic expressions.

• The record or records being modified are found by using a search condition. All rows that satisfy the search condition are updated. If no search condition is supplied, all rows in the table are updated.

Example: Change the benefits for the librarian Margaret Chapman from partial to full with this statement:

UPDATE EMPLOYEE
     SET    BENEFITS = 'FULL'
     WHERE  EID = 103 ;

The value 'FULL' is the change being made. It will replace the current value 'PART' listed in the BENEFITS column for Margaret Chapman, whose employee ID number is 103. A change is made for all records that satisfy the search condition; in this example, only one row is updated.

Example: The finance analysis needs to include the effects of a 10% pay raise to all staff; i.e., to all employees whose RANK is STAFF.

Use this statement to update all staff salaries with the intended raise:

UPDATE  EMP_RAISE
     SET     SALARY = SALARY * 1.1
     WHERE   RANK = 'STAFF' ;

If a portion of the EMP_RAISE table looked like this before the update:

EID  ENAME               DEPT   RANK   BENEFITS   SALARY
  101  Aster, John A.      MKT    STAFF  FULL       32000
  102  Barrington, Kyle    MGT    DHEAD  FULL       45000
  103  Chapman, Margaret   LIB    STAFF  PART       22000
  104  Jackson, Herbert    RND    STAFF  FULL       30000
  105  Price, Stella       FIN    DHEAD  FULL       42000
  106  Sanchez, Carla      MKT    STAFF  FULL       35000
  107  Smith, Roberta      RND    STAFF  PART       25000

It would look like this after the update operation:

EID  ENAME               DEPT   RANK   BENEFITS   SALARY
  101  Aster, John A.      MKT    STAFF  FULL       35200
  102  Barrington, Kyle    MGT    DHEAD  FULL       45000
  103  Chapman, Margaret   LIB    STAFF  PART       24200
  104  Jackson, Herbert    RND    STAFF  FULL       33000
  105  Price, Stella       FIN    DHEAD  FULL       42000
  106  Sanchez, Carla      MKT    STAFF  FULL       38500
  107  Smith, Roberta      RND    STAFF  PART       27500

Notice that only the STAFF rows are changed to reflect the increase. DHEAD row salaries remain as they were. As a word of caution, it's easy to "accidentally" modify all rows in a table. Check your statement carefully before executing it.