CODEWITHSUNDEEP
sqlmysql
Fero
Fero

Reputation: 13315

how can i able to delete the n'th ROW in a table using mysql query

Is there any way to delete the n'th ROW of a table using MYSQL?

Upvotes: 0

Views: 2585

Answers (4)

Pavlo Svirin
Pavlo Svirin

Reputation: 508

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.

Upvotes: 1

pavium
pavium

Reputation: 15118

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

Upvotes: 1

Matthew Scharley
Matthew Scharley

Reputation: 132284

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?

Upvotes: 0

David Andres
David Andres

Reputation: 31781

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
)

Upvotes: 0

Related Questions