Im trying to create a query that will delete any records for a user (preferably usrid=2034) where the record count is greater than 50 for each ctoon_id. I've had someone help with it so far, but it isn't incrementing the rnum value properly, so it doesn't know when to start pruning records.
DELETE FROM tbl_users_ctoons
WHERE (usrid, ctoon_id) IN (
SELECT DISTINCT usrid, ctoon_id
FROM (
SELECT @row_number := CASE
WHEN (@userid = usrid AND @ctoon_id = ctoon_id) THEN @row_number + 1
ELSE 1
END AS rnum, @userid:=usrid AS usrid, @ctoon_id:=ctoon_id AS ctoon_id
FROM tbl_users_ctoons
WHERE usrid=2034
ORDER BY usrid, ctoon_id
) sub
WHERE rnum > 50
);
WITH cte AS (
SELECT usrid, row_number() OVER (PARTITION BY Usrid, ctoon_id, ORDER BY Usrid) rnum
FROM tbl_users_ctoons
)
DELETE FROM cte
where rnum >= 50;
This would replace what you have. However, since I have not test data and results, I am guessing as to what is expected. Thus the comment about trying on test data.
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WITH cte AS ( SELECT usrid, row_number() OVER (PARTITION BY Usrid, ctoon_id' at line 1
