Databases
StackOverflow2013
Postsdbo
POUpdate one MySQL table with values from another
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUpdate one MySQL table with values from another
    text
    copied!<p>I'm trying to update one MySQL table based on information from another.</p> <p>My <code>original</code> table looks like:</p> <pre><code>id | value ------------ 1 | hello 2 | fortune 3 | my 4 | old 5 | friend </code></pre> <p>And the <code>tobeupdated</code> table looks like:</p> <pre><code>uniqueid | id | value --------------------- 1 | | something 2 | | anything 3 | | old 4 | | friend 5 | | fortune </code></pre> <p>I want to update <code>id</code> in <code>tobeupdated</code> with the <code>id</code> from <code>original</code> based on <code>value</code> (strings stored in <code>VARCHAR(32)</code> field).</p> <p>The updated table will hopefully look like:</p> <pre><code>uniqueid | id | value --------------------- 1 | | something 2 | | anything 3 | 4 | old 4 | 5 | friend 5 | 2 | fortune </code></pre> <p>I have a query that works, but it's very slow:</p> <pre><code>UPDATE tobeupdated, original SET tobeupdated.id = original.id WHERE tobeupdated.value = original.value </code></pre> <p>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 <code>value</code> will be slow, but this is the only data I have to match them together.</p> <p>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?</p> <p>I tried <a href="https://stackoverflow.com/questions/3384044/mysql-how-can-i-update-a-table-with-values-from-another-table">MySQL - How can I update a table with values from another table?</a>, but it didn't really help. Any ideas?</p> <p>Thanks in advance for helping a MySQL novice!</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload