Update one MySQL table with values from another

Question

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   Thanks in advance for helping a MySQL novice

User · Answer

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

User · Answer

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

[mysql] Update one MySQL table with values from another

Examples related to mysql

Examples related to sql