MySQL: UPDATE table with COUNT from another table?
mysql, sql
Solution
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)
Problem
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?