Updating a Table › Modifying Data in a Table with SET › Exercise 8-4

Exercise 8-4

Enter a SELECT statement to display the employees and vacation hours accrued. To check your SELECT statement, see Review Answers for Chapter 8. Jot down a couple of the employees and their vacation hours accrued so you can check them after you have made the changes.

To make the vacation hour changes, enter:

update benefits
    set vac_accrued = vac_accrued + 8
    where fiscal_year = 2000;

What message do you see?

You see a message specifying the number of rows that have been updated.

Display the Changes

Enter a SELECT statement sorted by employee id to display the updated table.

The result looks like this:

 EMP_ID  VAC_ACCRUED
 ------  -----------
   1003       100.00
   1034       100.50
   1234       100.00
   1765       100.50
   2004       100.50
   2010       100.75
   2096       100.50
   2106       100.50
   2174       100.00
   2180       100.50
   2209       100.50
   2246       100.50
   2424       100.50
   2437        76.00
   2448        76.00
   2461        76.00
   2466       100.50
   2598        68.00
   2781        76.00
   2894        76.00
   3082        76.00
   3118        76.00
   3222        76.00
   3288        76.00
   3294        76.00
   3338        76.00
   3341        76.00
   3411        76.00
   3433        76.00
   3449        76.00
   3704        76.00
   3764        76.00
   3767        76.00
   3769        76.00
   3778        76.00
   3841        76.00
   3991        76.00
   4001        76.00
   4002        76.00
   4008        76.00
   4027        76.00
   4321        76.00
   4358        76.00
   4456        76.00
   4660        76.00
   4703        54.75
   4773        76.00
   4962        76.00
   5008        54.50
   5090        54.00
   5103        54.00

 51 rows processed

Modifying Selected Rows

Often you want to change the value in a column only in rows that meet a certain search condition.

How It's Done

All employees who have accrued more than 80 hours of vacation time are supposed to have an additional eight hours added to their accrued vacation. To do this, add a WHERE clause to the previous statement:

update benefits
   set vac_accrued = vac_accrued + 8
   where vac_accrued > 80:ehp2
   and fiscal_year = 2000;

Display the Changes

Enter a SELECT statement to display the BENEFITS table to confirm the change.

The result looks like this:

 EMP_ID  VAC_ACCRUED
 ------  -----------
   2010       108.75
   2246       108.50
   1034       108.50
   2424       108.50
   2209       108.50
   1765       108.50
   2180       108.50
   2106       108.50
   2096       108.50
   2004       108.50
   1003       108.00
   1234       108.00
   2466       108.50
   2174       108.00

 14 rows processed

The only rows for which that column is updated are the rows that meet the condition.