108

I'm trying to update one MySQL table based on information from another.

My original table looks like:

id | value
------------
1  | hello
2  | fortune
3  | my
4  | old
5  | friend

And the tobeupdated table looks like:

uniqueid | id | value
---------------------
1        |    | something
2        |    | anything
3        |    | old
4        |    | friend
5        |    | fortune

I want to update id in tobeupdated with the id from original based on value (strings stored in VARCHAR(32) field).

The updated table will hopefully look like:

uniqueid | id | value
---------------------
1        |    | something
2        |    | anything
3        | 4  | old
4        | 5  | friend
5        | 2  | fortune

I have a query that works, but it's very slow:

UPDATE tobeupdated, original
SET tobeupdated.id = original.id
WHERE tobeupdated.value = original.value

This maxes out my CPU and eventually leads to a timeout with only a fraction of the updates performed (there are several thousand values to match). I know matching by value will be slow, but this is the only data I have to match them together.

Is there a better way to update values like this? I could create a third table for the merged results, if that would be faster?

I tried MySQL - How can I update a table with values from another table?, but it didn't really help. Any ideas?

starball
  • 20,030
  • 7
  • 43
  • 238
Superangel
  • 1,339
  • 2
  • 10
  • 12

2 Answers2

239
UPDATE tobeupdated
INNER JOIN original ON (tobeupdated.value = original.value)
SET tobeupdated.id = original.id

That should do it, and really its doing exactly what yours is. However, I prefer 'JOIN' syntax for joins rather than multiple 'WHERE' conditions, I think its easier to read

As for running slow, how large are the tables? You should have indexes on tobeupdated.value and original.value

EDIT: we can also simplify the query

UPDATE tobeupdated
INNER JOIN original USING (value)
SET tobeupdated.id = original.id

USING is shorthand when both tables of a join have an identical named key such as id. ie an equi-join - http://en.wikipedia.org/wiki/Join_(SQL)#Equi-join

wired00
  • 13,930
  • 7
  • 70
  • 73
  • 3
    Thanks wired00! This works perfectly. The tables are quite large (`original` is 100,000+ entries and `tobeupdated` 10,000+), so I took your and noodl's advice about the indexes and the whole query now finishes in under a second. I can't believe the difference!? Thanks so much for your help; I've learnt a lot! – Superangel Apr 20 '11 at 13:47
  • 5
    Thats great to hear :) I learn a lot on here too. I really like this site as you can be exposed to many different issues and ideas – wired00 Apr 20 '11 at 13:49
  • thank you.. I tried so many things from stackoverflow.. this one finally worked – Jaxx0rr May 05 '14 at 09:11
  • Just wanted to mention that a simple UPDATE with WHERE was much faster than the JOIN syntax. About 10.000 rows. – Alex2php Aug 22 '17 at 13:44
  • 1
    They key ingredient is of course assigning as index. took me to update 300K records in 4 seconds as opposed to timeouts without them. – Amjo Feb 19 '18 at 22:49
  • Thx @wired00 <3 – tRx May 16 '18 at 12:42
  • Is `inner join` doing anything here that a regular join wouldn't? (I'm about to run a similar query, but the two tables have identical columns.) – felwithe Aug 17 '18 at 13:51
  • Wonderful, worked like charm. Saved a lot of time bro! – Janib Soomro Aug 16 '19 at 17:07
0

It depends what is a use of those tables, but you might consider putting trigger on original table on insert and update. When insert or update is done, update the second table based on only one item from the original table. It will be quicker.

Jolta
  • 2,620
  • 1
  • 29
  • 42
firegnom
  • 833
  • 7
  • 20