MySQL UPDATE query with subquery input forever

Question

I have a MySQL UPDATE query that takes a lot of time. Am I missing a much simpler way to achieve the same result?

"UPDATE table2, table1
SET table2.id_occurrences = (SELECT SUM(IF(id = table2.id, 1, 0)) FROM table1)
WHERE table2.id = table1.id;"

• table2contains all possible values id, exactly one record for each.
• table1contains some values id, but there are several records of some values.
• I need to update entries in table2to show the number of occurrences of the corresponding value idin table1. The above query does the job, but takes about 3 minutes when table 1 contains 500 records and table230,000 records. I have much larger tables to process, so it takes too long :)

Thanks in advance.

Answer 1

Avoid subqueries, use joins:

UPDATE table2
LEFT JOIN table1 ON (table2.id = table1.id)
SET table2.id_occurrences = COUNT(table1.id)
GROUP BY table2.id

Oh, UPDATE does not support GROUP BY. Try this query:

UPDATE table2
LEFT JOIN (
   SELECT id, COUNT(*) AS cnt FROM table1 GROUP BY id
) AS t1
ON (table2.id = t1.id)
SET table2.id_occurrences = t1.cnt

Answer 2

I think your joining the update may not be necessary ...

UPDATE table2
    SET table2.id_occurrences = (SELECT COUNT(*) FROM table1
                                     WHERE table2.id = table1.id);

Answer 3

I would go for something like:

UPDATE table2
SET id_occurrences = (SELECT count(*) FROM table1
                      WHERE table1.id = table2.id)

Answer 4

UPDATE table2, table1 
SET table2.id_occurrences = (SELECT SUM(IF(id = table2.id, 1, 0)) FROM table1) 
WHERE table2.id in (select distinct table1.id from table1) AND table2.id = table1.id;