Hi i have tables like this :
table entry :
id | total_comments
_____________________
1 | 0
2 | 0
3 | 0
4 | 0
table comments :
id | eid | comment
_____________________
1 | 1 | comment sdfd
2 | 1 | testing testing
3 | 1 | comment text
4 | 2 | dummy comment
5 | 2 | sample comment
6 | 1 | fg fgh dfh
Query i write :
UPDATE entry
SET total_comments = total_comments + 1
WHERE id IN ( SELECT eid
FROM comments
WHERE id IN (1,2,3,4,5,6))
Results i get is :
table entry :
id | total_comments
_____________________
1 | 1
2 | 1
3 | 0
4 | 0
Expected results :
table entry :
id | total_comments
_____________________
1 | 4
2 | 2
3 | 0
4 | 0
Any help will be appreciated.
Use:
UPDATE entry
SET total_comments = (SELECT COUNT(*)
FROM COMMENTS c
WHERE c.eid = id
GROUP BY c.eid)
WHERE id IN ( SELECT eid
FROM comments
WHERE id IN (1,2,3,4,5,6))
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With