UPDATE
Like SELECT statements, UPDATE statements can consist of several clauses each with their own keyword
- IGNORE
- SET
- WHERE
- ORDER BY
- LIMIT
The simplest UPDATE statement would comprise "UPDATE tablename" with a SET clause to specify the changes to be made
SET clause
As with the alternative INSERT syntax, the SET clause specifies the columns and their new values
Example. Having decided on a staff reshuffle, clear all housemaster ids from the house table so new ones can subsequently be allocated
UPDATE house SET house_master = NULL;
No WHERE clause was specified so the house_master field is set to NULL for every record in the table. Be careful you do not omit a where clause by accident.
WHERE clause
This is used exactly the same way as in a select query. Only records where the condition in the WHERE clause is true will be affected by the update.
Change the current price of blazers to £79.99 ...
UPDATE price
SET price=79.99
WHERE itemid IN (1,2)
AND CURDATE() BETWEEN from_date AND to_date
... and reduce all other item prices by 10%
UPDATE price
SET price = price * 0.9
WHERE itemid NOT IN (1,2)
AND CURDATE() BETWEEN from_date AND to_date
We can, of course, combine the two queries above into a single update query:
UPDATE price
SET price = CASE
WHEN itemid IN (1,2) THEN 79.99
ELSE price * 0.9
END
WHERE CURDATE() BETWEEN from_date AND to_date
ORDER BY clause
Defines the order in which the records should be updated. This feature is often of use when there is a LIMIT clause.
LIMIT clause
Specifies that the updates should cease after a specified number of records have been updated.
Multiple Table Updates
You can use joins in UPDATE queries. This enables you to use other tables to provide values for the update or to match against so you can reference them in the WHERE clause.
As a temporary measure to aid identification of new pupils, the headmaster wants an asterisk appending to the last name of the ex-academy pupils
UPDATE pupil
INNER JOIN house USING (houseid)
SET lname = CONCAT(lname, '*')
WHERE house_name = 'Academy';
Dismayed that none of the pupils has chosen Computing as a subject, Beetsem-Daley decided that those pupils with a weak subject (i.e. scored less than 65% in the last exam) should drop that subject in favour of Computing.
We need to match the choice table against the result table. Where a choice record for a pupil/subject matches a result record that is lower than 65% in 2015 we update the subjectid in the choice table, replacing it with 10 (computing subject id). We use IGNORE to stop it failing in those cases where we would get a duplicate when a pupil has more than one weak subject.
UPDATE IGNORE
choice c
JOIN result r USING (pupilid, subjectid)
SET c.subjectid = 10
WHERE pcent < 65 AND schoolyear = 2015
Multiple Conditional Updates
Occasionally you will need to update several records where the update value is different for each record.
Example:
The headmaster wants each pupil's date of birth adding to the pupil records, so having added the new field to the record structure we now need to update them.
In this case, the date is different for each pupil. We could have a separate query for each pupil but it would be more efficient to use a single query.
Our query must update the records so that
- If id is 1 then the dob should be '2001-06-22'
- If id is 2 then the dob should be '2001-03-04'
- If id is 3 then the dob should be '2002-01-16'
- etc. for all pupils
We could use a CASE statement but an easier way to do it is with a multiple insert query using the ON DUPLICATE facility
INSERT IGNORE INTO pupil (pupilid, dob) VALUES
(1,'2001-06-22'), (2,'2001-03-04'), (3,'2002-01-16'), (4,'2001-08-02'), (5,'2001-10-01'),
(6,'2000-12-13'), (7,'2001-05-03'), (8,'2001-11-20'), (9,'2001-06-30'), (10,'2001-08-12'),
(11,'2001-08-24'), (12,'2001-09-06'), (13,'2001-09-03'), (14,'2001-09-30'), (15,'2001-09-08'),
(16,'2001-04-23'), (17,'2001-10-04'), (18,'2001-06-14'), (19,'2001-09-06'), (20,'2001-08-11'),
(21,'2001-09-18'), (22,'2001-04-26'), (23,'2001-05-06'), (24,'2001-12-08')
ON DUPLICATE KEY
UPDATE dob = VALUES(dob);
Note: IGNORE is used to stop the query failing because no values are provided for first and last names. Alternative you could specify those fields (pupilid, fname, lname, dob) and provide dummy values for them (1,'','','2001-06-22'),(2,'','','2001-03-04')
Calculating Age from Date of Birth
While we are processing dates of birth, this seems a good point have a look at age calculations
Note that we do not store "age" in database records as this would have to be updated on a regular basis. Instead, we calculate it from the date (date of birth in this instance, but it may be that you want, say, the age of an invoice in days)
MySQL provides a useful function for this, namely TIMESTAMPDIFF()
SELECT fname
, lname
, dob
, CURDATE()
, TIMESTAMPDIFF(YEAR, dob, CURDATE()) as age
FROM pupil
ORDER BY dob
+----------+------------+------------+------------+------+
| fname | lname | dob | CURDATE() | age |
+----------+------------+------------+------------+------+
| Caroline | Freeman | 2000-12-13 | 2016-05-07 | 15 |
| Allan | Blair | 2001-03-04 | 2016-05-07 | 15 |
| Margaret | Norton | 2001-04-23 | 2016-05-07 | 15 |
| Peter | Appleby | 2001-04-26 | 2016-05-07 | 15 |
| David | Powell | 2001-05-03 | 2016-05-07 | 15 |
| Wayne | Jones | 2001-05-06 | 2016-05-07 | 15 |
| Mary | Sheldon | 2001-06-14 | 2016-05-07 | 14 |
| Adam | Simms | 2001-06-22 | 2016-05-07 | 14 |
| George | Wilson | 2001-06-30 | 2016-05-07 | 14 |
| Anne | Bailey | 2001-08-02 | 2016-05-07 | 14 |
| Michael | Grove | 2001-08-11 | 2016-05-07 | 14 |
| Henry | Irving | 2001-08-12 | 2016-05-07 | 14 |
| Jane | Morrison | 2001-08-24 | 2016-05-07 | 14 |
| John | Tully | 2001-09-03 | 2016-05-07 | 14 |
| Mary | Whitehouse | 2001-09-06 | 2016-05-07 | 14 |
| John | Patterson | 2001-09-06 | 2016-05-07 | 14 |
| Jack | Williams | 2001-09-08 | 2016-05-07 | 14 |
| Peter | Adamson | 2001-09-18 | 2016-05-07 | 14 |
| John | Watson | 2001-09-30 | 2016-05-07 | 14 |
| Anthony | Bell | 2001-10-01 | 2016-05-07 | 14 |
| Mary | Blake | 2001-10-04 | 2016-05-07 | 14 |
| Emma | Watson | 2001-11-20 | 2016-05-07 | 14 |
| William | Smith | 2001-12-08 | 2016-05-07 | 14 |
| Anna | Hamilton | 2002-01-16 | 2016-05-07 | 14 |
+----------+------------+------------+------------+------+
As an aside, an invoice age in days would be
TIMESTAMPDIFF(DAY,invoice_date,CURDATE())
Next: Deleting records