26

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?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
coroner
  • 513
  • 2
  • 6
  • 13

1 Answers1

52

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.

Polynomial
  • 27,674
  • 12
  • 80
  • 107
  • 1
    Be very careful when you do this. The order of the select can change if you add an index or something like that. The SQL server make the most easy select it can find when not adding a where clause. – Romo Oct 08 '12 at 10:48
  • That is correct. The usual `ORDER BY` requirements apply. I've edited to reflect this. – Polynomial Oct 08 '12 at 10:50
  • Thanks for your answer! I tried this and unfortunately it only works when a condition is given. So I now use `DELETE FROM table WHERE a_field > 0 LIMIT X;` This works so far. Thank you :-) – coroner Oct 08 '12 at 10:52
  • @coroner Are you trying that in MySQL Workbench? If so, that's a safety limitation of the tool, not a limitation of MySQL itself. You should be able to delete without the condition. Just an FYI: code in StackExchange comments uses backticks, i.e. `\`some code\`` – Polynomial Oct 08 '12 at 10:54
  • @Polynomial: Thank you again! You solved all my problems :-) I indeed tried it with MySQL Workbench. ^^ – coroner Oct 08 '12 at 11:41