Solved

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

Posted on 2010-09-07
4
346 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 93

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 93

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

630 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