CODEWITHSUNDEEP
sql-server
susanthosh
susanthosh

Reputation: 450

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.

Upvotes: 1

Views: 329

Answers (4)

Quassnoi
Quassnoi

Reputation: 425251

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

Upvotes: 0

JonH
JonH

Reputation: 33141

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.

Upvotes: 0

Kevin Robatel
Kevin Robatel

Reputation: 8386

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;

Upvotes: 1

bobwah
bobwah

Reputation: 2568

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

Upvotes: 0

Related Questions