delete all but latest 6 rows

[Destramic]

hey guys i'm trying to delete all rows but the latest 6 but i'm having some trouble with the query if you could please help.

 

i've done some reading and have been llooking at simular queries to what i'm after, but i'm not succeeding.

 

here is what i have so far:

DELETE FROM benchmarks
WHERE (
	SELECT count(benchmark_id)
        FROM benchmarks
        WHERE name = 'Framework'
        ORDER BY timestamp ASC
       ) < 6

the error i get is:

 

Error Code: 1093. You can't specify target table 'benchmarks' for update in FROM clause

 

any help on where i'm going wrong would be great.

 

thank you guys

[Barand]

try something like this

DELETE benchmarks
FROM benchmarks
LEFT JOIN (
    SELECT id
    FROM benchmarks
    ORDER BY id desc 
    LIMIT 6
    ) lastsix USING (id)
WHERE lastsix.id IS NULL;

[gizmola]

MySQL has lots of limitations that it seems you can often simply trick the engine and get around in some other way.

 

Your approach won't work because you can't use a correlated subquery of the same table you are trying to DELETE from.

 

However, hiding this inside nested subqueries lets you get around this issue.

 

DELETE FROM benchmarks
  WHERE timestamp <= (
    SELECT timestamp
    FROM (
      SELECT timestamp
      FROM benchmarks
      ORDER BY timestamp DESC
      LIMIT 1 OFFSET 6 
    ) balias
  )

[Barand]

Your approach won't work because you can't use a correlated subquery of the same table you are trying to DELETE from.

I tested my query before posting - it does work. Provided it is unique, you can substitute the timestamp for the id.

[Psycho]

Why not this?

 

DELETE FROM benchmarks
WHERE id NOT IN (
    SELECT timestamp
    FROM benchmarks
    ORDER BY timestamp DESC
    LIMIT 1 OFFSET 6
)

[Barand]

The chances of the id matching a timestamp value are pretty slim

[Destramic]

thanks guys for you replies...just what i'm after!   

 

@phscho yours returned an error

 

 

Error Code: 1235. This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

 

strange really as my workbench is up to date

 

also

 

 

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

 

 i had to turn off safe updates on my mysql workbench...not sure if that is a mysql workbench thing or i would still have gotten the same error if i hadn't of turned off and executed from my php script?

 

 

thanks again much appreciated

[gizmola]

I tested my query before posting - it does work. Provided it is unique, you can substitute the timestamp for the id.

Hey Barand,

My comment was addressed to the original post, not to your answer. I actually didn't see your answer until after I had posted --- we were posting at approximately the same time I guess.