I have two tables that need the exact same values for denormalization purposes.
Here's the query.
first table
UPDATE Table_One SET win = win+1, streak = streak+1, score = score+200 WHERE userid = 1 AND lid = 1 LIMIT 1 second table
UPDATE Table_Two SET win = win+1, streak = streak+1, score = score+200 WHERE userid = 1 LIMIT 1 As you can see the only difference between both tables is their name and table two doesn't have the field lid
Anyway to combine both updates to just one?
It should be possible with a multi-table update, as described in the documentation.
http://dev.mysql.com/doc/refman/5.5/en/update.html
UPDATE Table_One a INNER JOIN Table_Two b ON (a.userid = b.userid) SET a.win = a.win+1, a.streak = a.streak+1, a.score = a.score+200, b.win = b.win+1, b.streak = b.streak+1, b.score = b.score+200 WHERE a.userid = 1 AND a.lid = 1 AND b.userid = 1 Note: Multi-table doesn't support LIMIT, so this could cause more grief depending on the details.
Stored procedures or transactions may be a nicer solution.
If there is a one to one or one to many relation from Table_One to Table_Two, this would work:
UPDATE Table_One T1, Table_Two T2 SET T1.win = T1.win+1, T1.streak = T1.streak+1, T1.score = T1.score+200, T2.win = T2.win+1, T2.streak = T2.streak+1, T2.score = T2.score+200 WHERE T1.userid = 1 AND T1.lid = 1 AND T2.userid = T1.userid; If you can join the tables, then you could create a view of two tables, then update via that view. In your example it looks like userid might be a suitable key.
In creating the view, you'd need to stick to the following guidelines.
They’re two separate queries and so must be treated as such. Sorry to say it, but if you’re updating two tables with identical data, there’s probably a better way to design your database. Remember to keep your programming DRY.
Edit: Should retract that; you can use it for multiple tables, but you can’t use ORDER BY or LIMIT.
1, but then you set to 0 when you need to “reset” the table.