Update row Count in MySQL-CodePudding

UPDATE my_reports AS A
INNER JOIN (SELECT id, COUNT(id) AS count FROM my_reports GROUP BY id) AS B
ON A.id = B.id
SET A.count = B.count;

This query is updating the count as 1 each row, because we have unique id, I don't want to count the id, but I want user_id(which is another column). Tried using Right Join but not able to update

id           user_id        count
1             33             ? (required 2)
2             33             ?  (required 2)
3             44             ? (required 1)
4             45             ? (required 1)

CodePudding user response：

CREATE TABLE myTable(id int, user_id int, count int);
INSERT INTO myTable VALUES (1, 33, null), (2, 33, null), (3, 44, null), (4,45, null);

So, you want to get the number of records per user_id, which is SELECT user_id, count(*) AS count FROM myTable GROUP BY user_id. You can join this to the myTable to update the count:

UPDATE myTable A 
 INNER JOIN (SELECT user_id, count(*) as count FROM myTable B GROUP BY user_id) B
    ON A.user_id = B.user_id
   SET A.count = B.count;

Here's a fiddle