Delete first X lines of a database

mysql sql-delete
coroner picture coroner · Oct 8, 2012 · Viewed 39.4k times · Source

is there an SQL command to delete the first X lines of a database table?

I have a database table containing some information but no id or auto-incrementing value and a program that processes the first X lines of this table. Afterwards these X lines need to be deleted. So the standard query is:

DELETE FROM table WHERE something = value;

So, is there a way to build a query like:

DELETE FROM table WHERE rownumber <= X;

I have tried this command, but nothing happens to the database.. Do you have any clue?

Answer

Polynomial picture Polynomial · Oct 8, 2012

Use LIMIT on your delete:

DELETE FROM table WHERE condition LIMIT 10

Or, if you don't want the condition

DELETE FROM table LIMIT 10

Remember that the order in which rows will be deleted is undefined - it depends on your DBMS configuration and table indices. You should include an ORDER BY so that the deletion is done in a defined order, e.g. ORDER BY id ASC to delete the lowest IDs first.

See the MySQL documentation for DELETE for more details.

Related questions

MySQL Error 1093 - Can't specify target table for update in FROM clause

I have a table story_category in my database with corrupt entries. The next query returns the corrupt entries: SELECT * FROM story_category WHERE category_id NOT IN ( SELECT DISTINCT category.id FROM category INNER JOIN story_category ON category_…

Read More
How can I roll back my last delete command in MySQL?

I accidentally deleted some huge number of rows from a table... How can I roll it back? I executed the query using PuTTY. I'll be grateful if any of you can guide me safely out of this...

Read More
How to delete from multiple tables in MySQL?

I am trying to delete from a few tables at once. I've done a bit of research, and came up with this DELETE FROM `pets` p, `pets_activities` pa WHERE p.`order` > :order AND p.`pet_id` = :pet_id …

Read More