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

[The above example was line wrapped for better viewing on this Web page.]

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.

   Show Answer

   update myemployees
     set lastname = 'Weber-Williams' 
     where firstname = 'Jonie' and lastname = 'Weber';
   select * from myemployees;

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

   Show Answer

   update myemployees
     set age = age+1
     where firstname = 'Dirk' and lastname='Smith';
   select * from myemployees;

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

   Show Answer

   update myemployees
     set title = 'Administrative Assistant'
     where title = 'Secretary';
   select * from myemployees;

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

   Show Answer

   update myemployees
     set salary = salary + 3500
     where salary < 30000;
   select * from myemployees;

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

   Show Answer

   update myemployees
     set salary = salary + 4500
     where salary > 33500;
   select * from myemployees;

6. All “Programmer II” titles are now promoted to “Programmer III”.

   Show Answer

   update myemployees
     set title = 'Programmer III'
     where title = 'Programmer II';
   select * from myemployees;

7. All “Programmer” titles are now promoted to “Programmer II”.

   Show Answer

   update myemployees
     set title = 'Programmer II'
     where title = 'Programmer';
   select * from myemployees;

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

Results will be displayed here