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).