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.