Solved

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

Posted on 2010-09-07
4
330 Views
Last Modified: 2012-05-10
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?
0
Comment
Question by:davideo7
  • 2
4 Comments
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 250 total points
ID: 33622270
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
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 250 total points
ID: 33622329
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
 
LVL 1

Assisted Solution

by:dolomitedave
dolomitedave earned 125 total points
ID: 33622338
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
 
LVL 2

Assisted Solution

by:wnaude
wnaude earned 125 total points
ID: 33776600
read them into a new table delete the old table copy back to old table
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question