Updating cached counts in MySQL
- by phleet
In order to fix a bug, I have to iterate over all the rows in a table, updating a cached count of children to what its real value should be. The structure of the things in the table form a tree.
In rails, the following does what I want:
Thing.all.each do |th|
Thing.connection.update(
"
UPDATE #{Thing.quoted_table_name}
SET children_count = #{th.children.count}
WHERE id = #{th.id}
"
)
end
Is there any way of doing this in a single MySQL query?
Alternatively, is there any way of doing this in multiple queries, but in pure MySQL?
I want something like
UPDATE table_name
SET children_count = (
SELECT COUNT(*)
FROM table_name AS tbl
WHERE tbl.parent_id = table_name.id
)
except the above doesn't work (I understand why it doesn't).
