Is there any way to delete the n'th ROW of a table using MYSQL?
-
4You should NEVER assume that the rows in a table are stored in a specific order. So the n'th row is depending on what you order the rows buy.idstam– idstam2009-09-08 05:53:53 +00:00Commented Sep 8, 2009 at 5:53
Add a comment
|
4 Answers
The SQL query DELETE allows you to say
DELETE FROM tablename WHERE column=value
but you have to specify the rows to be deleted by means of some property of the rows, not by counting the rows.
If you knew the contents of the row you want to delete, you should be able to use that knowledge in the WHERE clause.
It might be WHERE id=somenumber
or
WHERE column1=somevalue AND column2=someothervalue
Comments
DELETE FROM WHERE id>9 ORDER BY id LIMIT 1
2 Matthew Scharley: you cannot delete the records and select the records in the subquery for WHERE in the same big query. This is MySQL specific.
Comments
This is a bit sloppy and I'm not all that familiar with MySQL-specific queries. You could use the limit statement to find and insert that record into a temp table and then perform a delete.
For example (excuse the mess if this doesn't work):
INSERT INTO SomeTempTable(Id)
SELECT Id
FROM ATable
LIMIT N, 1
DELETE
FROM ATable
WHERE Id IN
(
SELECT Id
FROM SomeTempTable
)
Comments
I can't get this to work in one query, but:
SELECT `primary_key` FROM `table` LIMIT n-1, 1;
DELETE FROM `table` WHERE `primary_key` = the primary key from the select
Is there some reason you're not using the primary key directly though, instead of counting rows?
