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.
-
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';
-
Dirk Smith's birthday is today, add 1 to his age:
UPDATE myemployees_ts0211 SET age = age + 1 WHERE firstname = 'Dirk' AND lastname = 'Smith';
-
All secretaries are now called "Administrative Assistant". Update all titles accordingly:
UPDATE myemployees_ts0211 SET title = 'Administrative Assistant' WHERE title = 'Secretary';
-
Everyone making under 30000 are to receive a 3500 a year raise:
UPDATE myemployees_ts0211 SET salary = salary + 3500 WHERE salary < 30000;
-
Everyone making over 33500 are to receive a 4500 a year raise:
UPDATE myemployees_ts0211 SET salary = salary + 4500 WHERE salary > 33500;
-
All "Programmer II" titles are now promoted to "Programmer III":
UPDATE myemployees_ts0211 SET title = 'Programmer III' WHERE title = 'Programmer II';
-
All "Programmer" titles are now promoted to "Programmer II":
UPDATE myemployees_ts0211 SET title = 'Programmer II' WHERE title = 'Programmer';