Delete duplicate entries from Database in SQL server 2008 [duplicate]

Question

I am using the SQL Server 2008R2, I have the following table named Emps

ID      Name      Title   
 1      XYZ       Manager
 2      ABC       CEO
 3      LMP       Clerk
 4      XYZ       Manager
 5      XYZ       Manager

Data in row 1,4 and 5 are same, Now what i want is to keep only one entry and delete all other existence of the duplicate record, one thing i should mention is my data is already inserted into database and i have to remove the duplicate data. Kindly Guide me is there any solution for it.

Devart · Accepted Answer · 2013-09-02 10:56:18Z

7

Try this one -

DECLARE @temp TABLE
(
      ID INT IDENTITY(1,1)
    , Name VARCHAR(10)
    , Title VARCHAR(10)
)

INSERT INTO @temp (Name, Title)
VALUES 
    ('XYZ', 'Manager'),
    ('ABC', 'CEO'),
    ('LMP', 'Clerk'),
    ('XYZ', 'Manager'),
    ('XYZ', 'Manager')

DELETE FROM t
FROM (
    SELECT 
          Name
        , Title
        , rn = ROW_NUMBER() OVER (PARTITION BY Name, Title ORDER BY 1/0) 
    FROM @temp
) t
WHERE rn > 1

SELECT * 
FROM @temp

Query cost -

enter image description here

Output -

ID          Name       Title
----------- ---------- ----------
2           ABC        CEO
3           LMP        Clerk
4           XYZ        Manager

answered Sep 2, 2013 at 10:56

Devart

120k23 gold badges172 silver badges189 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

juergen d · Accepted Answer · 2013-09-02 10:52:02Z

7

delete from emps
where id not in
(
  select min(id) from emps
  group by name, title
)

answered Sep 2, 2013 at 10:52

juergen d

205k40 gold badges303 silver badges377 bronze badges

Collectives™ on Stack Overflow

Delete duplicate entries from Database in SQL server 2008 [duplicate]

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related