Updating Records

The update statement is used to update or change records that match a specified criteria. This is accomplished by carefully constructing a where clause.

update "tablename"
set "columnname" =
 "newvalue"
[,"nextcolumn" =
"newvalue2"...]
where "columnname"
OPERATOR "value"
[and|or "column"
OPERATOR "value"];
[] = optional

Examples:

update phone_book set area_code = 623 where prefix = 979;

update phone_book set last_name = 'Smith', prefix=555, suffix=9292 where last_name = 'Jones';

update employee set age = age+1 where first_name='Mary' and last_name='Williams'

Update statement exercises

After each update, issue a select statement to verify your changes.

1. Jonie Weber just got married to Bob Williams. She has requested that her last name be updated to Weber-Williams.
2. Dirk Smith's birthday is today, add 1 to his age.
3. All secretaries are now called "Administrative Assistant". Update all titles accordingly.
4. Everyone that's making under 30000 are to receive a 3500 a year raise.
5. Everyone that's making over 33500 are to receive a 4500 a year raise.
6. All "Programmer II" titles are now promoted to "Programmer III".
7. All "Programmer" titles are now promoted to "Programmer II".

Create at least 5 of your own update statements and submit them.

1. Jonie Weber just got married to Bob Williams. She has requested that her last name be updated to Weber-Williams.

   update
   myemployees_ts0211
   set lastname=
   'Weber-Williams'
   where firstname=
       'Jonie'
   and lastname=
      'Weber';

2. Dirk Smith's birthday is today, add 1 to his age.

   update myemployees_ts0211
   set age=age+1
   where firstname='Dirk' and lastname='Smith';

3. All secretaries are now called "Administrative Assistant". Update all titles accordingly.

   update myemployees_ts0211
   set title = 'Administrative Assistant'
   where title = 'Secretary';

4. Everyone that's making under 30000 are to receive a 3500 a year raise.

   update myemployees_ts0211
   set salary = salary + 3500
   where salary <>

5. Everyone that's making over 33500 are to receive a 4500 a year raise.

   update myemployees_ts0211
   set salary = salary + 4500
   where salary > 33500;

6. All "Programmer II" titles are now promoted to "Programmer III".

   update myemployees_ts0211
   set title = 'Programmer III'
   where title = 'Programmer II'

7. All "Programmer" titles are now promoted to "Programmer II".

   update myemployees_ts0211
   set title = 'Programmer II'
   where title = 'Programmer'