Delete everything from table except the top 100 rows

[JimFL]

Hi ,
Does anyone know the mysql syntax to delete everything in a table except the top 100 rows?

Do you need to use LIMIT?

Please help.

JimFl

 

[cLFlaVA]

how do you specify "top 100 rows"? is it the 100 highest unique identifiers? the 100 oldest dates?

*cLFlaVA
----------------------------
[tt]I already made like infinity of those at scout camp...[/tt]
beware of active imagination: [URL unfurl="true"]http://www.coryarthus.com/[/url]

BillyRayPreachersSonIsTheLeetestHax0rDude

 

[JimFL]

It could be either of those. I am looking to remove everything in a table except the 100 newest dates or 100 newest id's it doesnt really matter. For this example either would help me.

 

[cLFlaVA]

well, for example, if you are using a version of mysql that allows for subqueries, you could do this:

delete
  from mytable
 where row_id not in (
         select row_id
           from mytable
       order by row_id desc
          limit 100 )

that says, delete all records in mytable where the id is not in the highest 100 ids.

*cLFlaVA
----------------------------
[tt]I already made like infinity of those at scout camp...[/tt]
beware of active imagination: [URL unfurl="true"]http://www.coryarthus.com/[/url]

BillyRayPreachersSonIsTheLeetestHax0rDude

 

[JimFL]

Ok, I think my version does support subqueries so I will give that a go.

Thanks

 

[JimFL]

or maybe not. Is there another way to do a similar thing but without a subquery?

 

[r937]

run two queries

the first one:

select datecol
  from daTable
order
    by datecol desc
limit 100

make a note of the value that the 100th row returned

second query:

delete
  from daTable
 where datecol < [i]value from 1st query[/i]

r937.com | rudy.ca

 

[theScien]

Unrelated to the topic, but I still have to ask;

What version do you run that don't support sub-queries?

And;

How slow are sub-queries, comparing with having to make two different queries?

Thanks.

 

[TonyGroves]

Sub-queries were not supported before MySQL 4.1. Sub-queries would generally be faster, as there is only one compilation process, one data-transmission process, and the server can optimise for the entire query. With separate queries, the problem is often not so much one of speed, but the risk that the data might have changed in the interval between the two queries.

 

[theScien]

Good point, is locking possible at all to solve the data changing?

I'll have to find a way to get 4.1 or even 5.0 to work now.