1

I have a problem updating ~3.7 million records in the MySQL database. I have two tables - a_names and a_records and I need to update records with new names from names table. As for now, the best I can think about is using LEFT JOIN, but the updates take forever and usually results in a timeout. There are 15.4k records in a_names table.

UPDATE a_records AS a
LEFT JOIN a_names AS b
ON a.name = b.old_name
SET a.name = b.new_name;

I have tried suggested solutions from this answer, however using LIMIT in my query results in an error:

UPDATE a_records AS a
LEFT JOIN a_names AS b
ON a.name = b.old_name
SET a.name = b.new_name
WHERE a.id IN (
    SELECT a.id
    FROM a_records as a
    LEFT JOIN a_names as b
    ON a.name = b.old_name
    WHERE a.name <> b.new_name
    LIMIT 10
)

Query 1 ERROR: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

I am using 8.0.29-0ubuntu0.22.04.2 version of MySQL, which is AFAIK the latest release.

I also cannot use JOIN instead of updating the records as suggested in the given answer. It's not possible due to the technical limitations of the end users' software.

I am also thinking about writing a small script that is gonna download data from SQL, join and update data locally and upload them chunk-by-chunk in some temp table. After the whole update is done, I will replace the origin with the temp table. But I would like to stick to the SQL to learn something new.

Could you please help me? Thanks

FN_
  • 715
  • 9
  • 27
  • Please also provide the SQL statement that leads to this error. (WARNING: answers exist on stackoverflow for this problem!, see: [MySQL - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery](https://stackoverflow.com/questions/17892762/mysql-this-version-of-mysql-doesnt-yet-support-limit-in-all-any-some-subqu)) – Luuk Jul 22 '22 at 16:05
  • Does this answer your question? [MySQL - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery](https://stackoverflow.com/questions/17892762/mysql-this-version-of-mysql-doesnt-yet-support-limit-in-all-any-some-subqu) – Luuk Jul 22 '22 at 16:24
  • @Luuk well, SELECT is not the same as UPDATE, but it helped me to solve the issue, thanks. – FN_ Jul 22 '22 at 16:25

1 Answers1

0

After adjusting the query based on the given suggestions, this one is working OK so far

UPDATE a_records AS a
LEFT JOIN (
    SELECT a.id, b.new_name
    FROM a_records AS a
    LEFT JOIN a_names AS b
    ON a.name = b.old_name
    WHERE a.name <> b.new_name
    LIMIT 10000
) AS a2
ON a.id = a2.id
SET a.name = a2.new_name
WHERE a2.id IS NOT NULL

I will try to tweak the query a bit and post updated.

FN_
  • 715
  • 9
  • 27