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 *