3. Updating Records

The UPDATE statement is used to modify existing records in a table. It allows you to update one or more columns in rows that match a specified condition.

UPDATE "tablename"
SET "columnname" = "newvalue"
    [, "nextcolumn" = "newvalue2", ...]
WHERE "columnname" OPERATOR "value"
    [AND|OR "column" OPERATOR "value"];

The WHERE clause is essential to target specific records that meet the given criteria. Without it, all records in the table will be updated.

3.1. Examples

Consider the phone_book table:

UPDATE phone_book
SET area_code = 623
WHERE prefix = 979;

This example updates the area code to 623 for all records with a prefix of 979.

UPDATE phone_book
SET last_name = 'Smith', prefix = 555, suffix = 9292
WHERE last_name = 'Jones';

This example updates multiple columns (last_name, prefix, and suffix) for records where the last name is 'Jones'.

UPDATE employee
SET age = age + 1
WHERE first_name = 'Mary' AND last_name = 'Williams';

This example increments the age by 1 for the employee named Mary Williams.

3.2. Update Statement Exercises

After performing each update, use a SELECT statement to verify the changes.

1. Jonie Weber just got married to Bob Williams. Update her last name 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 making under 30000 are to receive a 3500 a year raise:

   UPDATE myemployees_ts0211
   SET salary = salary + 3500
   WHERE salary < 30000;
   

5. Everyone 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';