SQLTeam.com | Weblogs | Forums

DELETE records when certain parameters are met


#1

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
);


#2

Something like this? Please try on dummy data.

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;

#3

Am I replacing a portion of the query I posted with this, or using this as standalone?


#4

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.


#5

I tried it on my test table and got this error

#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

#6

Sorry this is a Microsoft SQL Server forum and I do not know MySQL