Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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 !!!

share|improve this answer
    
is it COUNT(*) ? –  All Dec 15 '13 at 5:31
    
I usually use COUNT(1). COUNT(*) is fine. –  RolandoMySQLDBA Dec 15 '13 at 5:34
    
does it have any advantage? I use COUNT(*) as I've read it is faster in innoDB comparing with other possibilities including COUNT(primary_key). –  All Dec 15 '13 at 5:35
2  
COUNT(PRIMARY KEY) is OK as well because a PRIMARY KEY column is required to be NOT NULL. COUNT(1) lets you forget about worrying about whether a column has a NULL or not. There is no real performance gain : dba.stackexchange.com/questions/22706/… –  RolandoMySQLDBA Dec 15 '13 at 5:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.