Solved

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

Posted on 2010-09-07
4
340 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article discusses how to implement server side field validation and display customized error messages to the client.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

751 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