MySQL: UPDATE table with COUNT from another table?

Question

I thought this would be simple but I can't get my head around it...

I have one table tbl1 and it has columns id,otherstuff,num.

I have another table tbl2 and it has columns id,info.

What I want to is make the num column of tbl1 equal to the number of rows with the same id in tbl2. Kind of like this:

UPDATE tbl1 SET num =
(SELECT COUNT(*) FROM tbl2 WHERE id=tbl1.id)

Any ideas?

CloudyMarble · Accepted Answer · 2013-04-17 12:18:54Z

17

If your num column is a valid numeric type your query should work as is:

UPDATE tbl1 SET num = (SELECT COUNT(*) FROM tbl2 WHERE id=tbl1.id)

answered Apr 17, 2013 at 12:18

CloudyMarble

37.7k70 gold badges103 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

paul · Accepted Answer · 2013-04-17 12:18:04Z

12

UPDATE tbl1, (select id, count(*) as idCount from tbl2 group by id) as t2
SET    tbl1.num = t2.idCount
WHERE  tbl1.id = t2.id;

answered Apr 17, 2013 at 12:18

paul

22.1k1 gold badge56 silver badges54 bronze badges

This was the perfect solution that is extremely fast on large tables. Thank you.

what does this do when there are 0 corresponding columns for a tbl1 row?