Tutorials

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