32

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?

Avag Sargsyan
  • 2,437
  • 3
  • 28
  • 41
user962449
  • 3,743
  • 9
  • 38
  • 53

4 Answers4

61

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.

rrehbein
  • 4,120
  • 1
  • 17
  • 23
11

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;
Atonewell
  • 922
  • 4
  • 14
0

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.

James Bradbury
  • 1,708
  • 1
  • 19
  • 31
-1

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.

Zoe Edwards
  • 12,999
  • 3
  • 24
  • 43
  • I see what you mean, but for our case we reset score for table 1 ever so often. Table two are the permanent scores, that's why we need two tables. It's just that I have 5 update queries in a row (batch process) and was wondering if I could limit the trips to the db. – user962449 Jan 06 '12 at 22:55
  • 1
    Would it not be easier to add a field to the table that does the same job, which by default is `1`, but then you set to `0` when you need to “reset” the table. – Zoe Edwards Jan 07 '12 at 16:35
  • @user962449 Came across this while searching for a similar solution. This answer is wrong - and is noted as such by the author. You should accept a better answer further down. –  May 18 '14 at 23:00
  • @user1864610 Stumbled across this myself too. While it's technically feasible, it's probably not a good idea. The necessity of doing so usually indicates a broken design (this can mean a variety of flaws, e.g. missing abstractions). Thomas was pretty much right with _DRY_ here. – Powerslave Jul 09 '16 at 23:45