How to UPDATE a column by COUNT of FK from another table?

Question

How to extend this query for the entire table, to update all ids?

UPDATE table1 SET number=(
SELECT COUNT(*) FROM table2 where id=1
) WHERE id=1

id is the PRIMARY KEY in table1 and FK in table2.

How to remove the WHERE clause to update each row of table1 by counting the number of rows in table2 with the same FK id?

Answer 1

You can do an UPDATE JOIN of table1 against a subquery that aggregates id counts in table2

UPDATE
    table1 A INNER JOIN
    (SELECT id,COUNT(1) idcount
    FROM table2 GROUP BY id) B
    USING (id)
SET A.number = B.idcount;

This query will not get every id. Why? If there is an id in table1 that is missing in table2, that does not write a zero in the numbers column.

To cover for id values missing in table2, run this one:

UPDATE
    table1 A LEFT JOIN
    (SELECT id,COUNT(1) idcount
    FROM table2 GROUP BY id) B
    USING (id)
SET A.number = IFNULL(B.idcount,0);

Give it a Try !!!