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)

About Ricard Torres

Senior Front-end Software Engineer at Netcentric, from Barcelona, Haidong Gumdo Instructor (korean martial art of the sword), street photographer, travel lover, TV addict, Boston Red Sox fan, and privacy advocate.

@ricard_dev @ricard_dev

📝 Blog 🎙 Podcast

Leave a Reply

Add <code> Some Code </code> if you need to.