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?
解决方案
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)