Updating cached counts in MySQL

Question

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

Answer 1

your subselect update should work; let's try touching it up a bit:

UPDATE table_name
  SET children_count = (
    SELECT COUNT(sub_table_name.id) 
      FROM sub_table_name 
      WHERE sub_table_name.parent_id = table_name.id
  )

Or if the sub-table is the same table:

UPDATE table_name as top_table
  SET children_count = (
    SELECT COUNT(sub_table.id) 
      FROM (select * from table_name) as sub_table
      WHERE sub_table.parent_id = top_table.id
  )

But that's not super efficient I'm guessing.

Answer 2

You probably got this error, right?

ERROR 1093 (HY000): You can't specify target table 'table_name' for update in FROM clause

The easiest way around this is probably to select the child counts into a temporary table, then join to that table for the updates.

This should work, assuming the depth of the parent/child relationship is always 1. Based on your original update this seems like a safe assumption.

I added an explicit write lock on the table to assure that no rows are modified after I create the temp table. You should only do this if you can afford to have it locked for the duration of this update, which will depend on the amount of data.

lock tables table_name write;

create temporary table temp_child_counts as
select parent_id, count(*) as child_count
from table_name 
group by parent_id;

alter table temp_child_counts add unique key (parent_id);

update table_name
inner join temp_child_counts on temp_child_counts.parent_id = table_name.id
set table_name.child_count = temp_child_counts.child_count;

unlock tables;