MySql update two tables at once

Question

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?

Answer 1

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.