MySQL: How can I delete all rows after the first 50 returned?

Basically I want to delete all rows after the first 50 returned.

So here's an example, here's a query, all of the results I want to keep, any row after this I'd like to have deleted:
SELECT * FROM game_shouts WHERE game_id=478 ORDER BY time DESC LIMIT 50

So any row past row 50 that has a game_id of 478 I'd like to delete with a separate query, how would I do this?
davideo7Asked:
Who is Participating?
 
Patrick MatthewsCommented:
May be slow :)

DELETE FROM game_shouts
WHERE time IN
    (SELECT time
    FROM game_shouts
    WHERE game_id=478
    ORDER BY time DESC
    LIMIT 50)
0
 
Patrick MatthewsCommented:
I may have misread the question.  Perhaps you meant:


DELETE FROM game_shouts
WHERE time NOT IN
    (SELECT time
    FROM game_shouts
    WHERE game_id=478
    ORDER BY time DESC
    LIMIT 50)

Open in new window

0
 
dolomitedaveCommented:
you could use LIMIT and OFFSET

SELECT * FROM game_shouts WHERE game_id=478 ORDER BY time DESC LIMIT 9999 OFFSET 50;

only problem is limit has to be higher than all your records
0
 
wnaudeCommented:
read them into a new table delete the old table copy back to old table
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.