How to DELETE with LIMIT and OFFSET in MySQL

It has been ages since I needed to write MySQL queries. I barely remember the basics.

I found myself perplexed after learning DELETE does not allow you to specify a limit. I thought it simply acted as a SELECT but the results instead of being returned where deleted.

It seems you need to nest 2 queries in order to fool MySQL. This seems overkill but it does the job.

DELETE FROM `my_table` WHERE id 
  IN (select id from 
      (select id FROM `my_table` 
       ORDER BY `my_field` DESC 
       LIMIT 20, 50) 
  x)Code language: SQL (Structured Query Language) (sql)

Leave a Reply

Your email address will not be published. Required fields are marked *