Deleting duplicate records in a table

Consider that I am having a table named A. In it I am having only one column named marks. It has some duplicated values. How can I delete the duplicate values without temporary table. And the table should 开发者_开发问答contain one of the duplicated values.

---

SELECT * FROM A INNER JOIN A as B WHERE A.marks = B.marks;

This show the duplicated values (if I don't make mistakes), so maybe you can do a JOIN for a DELETE?

EDIT: I juste try this, it doesn't work, don't you have a ID column in your table? like:

SELECT *
FROM A INNER JOIN A as B ON A.marks = B.marks
WHERE  A.id != B.id;

---

If two records are equal in the table I am unsure how you could identify one uniquely to delete just that item. What you could do is:

• delete both and then add one back in probably using a cursor or looping structure.
• select into another table and group by so you don't get duplicates, then delete all the rows from the original and copy these across.

• add an id column to this table, populate it then call:

  DELETE FROM A WHERE ( id NOT IN (SELECT MAX(id) FROM A GROUP BY name) )

and you could remove this column afterwards

---

The reason why it is so difficult to delete the duplicates susantosh is because you have not defined any key in your table so what makes any of your rows unique? You will need to pick up a basic book on table design and realize that without some sort of uniqueness you will drive yourself insane trying to figure out anything.

There are a few things you can do:

1. SELECT DISTINCT marks FROM TableName INTO NewTableName
2. GROUP BY a specific marks move them to another table and then delete the prev table
3. Add a UNIQUE Identity column (maybe of int type) then write code to get rid of the duplicates

Those are just some of your options, but without understanding the basics of table design you will run into this issue over and over.

---

WITH    q AS
        (
        SELECT  *, ROW_NUMBER() OVER (PARTITION BY marks ORDER BY marks) AS rn
        FROM    a
        )
DELETE
FROM    q
WHERE   rn > 1