Jump to content

delete all but latest 6 rows


Go to solution Solved by Barand,

Recommended Posts

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

Link to post
Share on other sites

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
  )
Link to post
Share on other sites

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.

  • Like 1
Link to post
Share on other sites

Why not this?

 

DELETE FROM benchmarks
WHERE id NOT IN (
    SELECT timestamp
    FROM benchmarks
    ORDER BY timestamp DESC
    LIMIT 1 OFFSET 6
)
Link to post
Share on other sites

thanks guys for you replies...just what i'm after!  :happy-04: 

 

@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

Link to post
Share on other sites

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.

  • Like 1
Link to post
Share on other sites
This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.