我有一个MySQL UPDATE查询,需要很长时间才能完成。我是否错过了一种更简单的方法来获得相同的结果?
"UPDATE table2, table1 SET table2.id_occurrences = (SELECT SUM(IF(id = table2.id, 1, 0)) FROM table1) WHERE table2.id = table1.id;"
table2
id
table1
提前致谢。
避免子查询,请使用联接:
UPDATE table2 LEFT JOIN table1 ON (table2.id = table1.id) SET table2.id_occurrences = COUNT(table1.id) GROUP BY table2.id
哦,UPDATE不支持GROUP BY。试试这个查询:
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