MySQL Forums
Forum List  »  Newbie

DELETE records when certain parameters are met
Posted by: Aaron Higgins
Date: February 17, 2017 09:22AM

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

<img src="http://forums.sqlteam.com/uploads/sqlteam/original/2X/a/ae1e522bf1659de8011fad37600d94b471270995.jpg"; width="170" height="500">

Options: ReplyQuote


Subject
Written By
Posted
DELETE records when certain parameters are met
February 17, 2017 09:22AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.