MySql update two tables at once

mysqlsql-update

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?

Best Solution

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.