MySQL – UPDATE from (Hidden) SELECT
May 26, 2009 by Matthew Boehm, Sr. Database Administrator in Tech Stuff, Tips and Tricks
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
After reading our last few posts, you know 
Last week, Bruce Eric Anderson visited our H2 Data Center in Houston with a team of colleagues from Dell to check out the unboxing and installation of a new rack of Dell’s PowerEdge710 servers, which feature Intel’s Nehalem processor. As any great evangelist would do, he posted an article about his visit on Dell’s “
Today, February 13, 2009, at exactly 5:31:30 p.m. CST, Unix users around the world will celebrate a momentous occasion: 1234567890 seconds will have passed since Unix time began at midnight UTC on January 1, 1970.
“What counters are important when troubleshooting SQL Server with Windows’ Perfmon (Performance Monitor)?”
The Planet has five core values. We are quizzed about them at companywide meetings, and they permeate our daily operations. I recently came across a passage when rereading “The Art of War” that may serve as a secondary set of core values for the team that handles The Planet’s new