The Planet Blog

Often, developers and administrators come across situations in which the only apparent solutions are overly complex and require significant effort to implement. Most of the time this happens because they don’t know something else – and usually something easier – exists.

This practice was evident in a recent case where a customer didn’t know it was possible to do table joins inside an UPDATE statement.

Here’s an example of a task he was faced with: You have a user table and a user_subscription table; the former contains information about each user, while the latter contains start and end dates for particular subscription products for each user. You want to extend the access period for each user that lives in the UK using product #5.

A common way of tackling this is to create a temporary table and populate it with all of the userIds from the user table that has the country = 'UK' property, then update the subscription table specifying only those userIds in the temporary table:

CREATE TEMPORARY TABLE ukUsers (userId INT(11));
INSERT INTO ukUsers SELECT userId FROM user WHERE country = 'UK';
UPDATE user_subscription 
  SET expireDate = DATE_ADD(expireDate, INTERVAL 3 DAY)
  WHERE userId IN (SELECT * FROM ukUsers) AND productId = 5;

While the code outlined above gets the job done, it could be better written. Let’s see how the SELECT statement could be used to retrieve the user information (i.e. get all UK users with subscription to product #5):

SELECT u.firstName, u.lastName
FROM user u LEFT JOIN user_subscription s USING(userId)
WHERE u.region = 'UK' AND s.productId = 5;

Now, let’s translate the above get into an UPDATE statement:

UPDATE user u LEFT JOIN user_subscription s USING(userId)
SET s.expireDate = DATE_ADD(s.expireDate, INTERVAL 3 DAY)
WHERE u.region = 'UK' AND s.productId = 5;

It’s as simple as that! We no longer need the temporary table, and it saves us a couple of extra SQL commands.

For more information on MySQL UPDATE syntax, visit: http://dev.mysql.com/doc/refman/5.1/en/update.html

-Matthew

This entry was posted on Tuesday, May 26th, 2009 at 5:54 pm and is filed under Tech Stuff, Tips and Tricks. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.