-2

I have a table candidate. It contains two columns name and roll. The values in all three rows of data are AA and 10. Now I want to delete only the second row. How can I do it?

Say we have an invoice number which automatically updates, but for some reason it has not updated and continued to register the same number again. In such case if I wish to delete the second row, what should I do?

I used this query:

delete from candidate where (SELECT * FROM candidate limit 1,1);

...but it throws an error saying

operand should contain one column

2
  • Why doesn't the table have a primary key? Sep 21, 2014 at 9:27
  • "Now I want to delete only the second row." The second row, according to what order? Apr 8, 2019 at 8:09

2 Answers 2

0

I hope this ll give you what u r looking for.

  
with rnCTE AS
(
  SELECT *, rn = ROW_NUMBER() OVER (ORDER BY (SELECT 0))
  FROM candidate
)
DELETE
FROM rnCTE
WHERE rn = 2

Or

DELETE  rw
FROM    (
        SELECT  rn = ROW_NUMBER() OVER (ORDER BY (SELECT 0))
        FROM  candidate
        ) AS rw
WHERE   rw.rn = 2

For My Sql



delete from candidate
where name = 'AA' and roll= '10'
limit 1
0
0
where (SELECT * FROM candidate limit 1,1)

..is wrong. You need to use:

where some_column IN (select other_column ...)

But don't do that with MySQL. MySQL is horribly bad at using sub-queries. Use a join instead. - a-horse-with-no-name

Not the answer you're looking for? Browse other questions tagged or ask your own question.