sql server 2008 deleting duplicates

Question

i have duplicates like this

col1, col2
1, alex
1, alex
2, liza
2, liza
3, peter
3, peter

there are only two of each. how do i delete the duplicates?

Are there any other columns available to distinguish between the rows? — Joe Stefanelli, Mar 28 '11 at 15:36
possible duplicate of [SQL - How can I remove duplicate rows?](http://stackoverflow.com/questions/18932/sql-how-can-i-remove-duplicate-rows) — Martin Smith, Mar 28 '11 at 15:41
@Martin: "how to delete duplicates" seems to be the most duplicate question ever. — Quassnoi, Mar 28 '11 at 15:58

score 12 · Accepted Answer · answered Mar 28 '11 at 15:38

12

WITH    q AS
        (
        SELECT  *,
                ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col1, col2) AS rn
        FROM    mytable
        )
DELETE
FROM    q
WHERE   rn >= 2

See here:

Deleting duplicates

answered Mar 28 '11 at 15:38

Quassnoi

413,100
91
616
614

3

+1: Learned something new. I didn't know you could use a CTE as the target of a delete operation. – Joe Stefanelli Mar 28 '11 at 15:43

RollingBoy · Answer 2 · 2011-03-28T15:44:52.283

1

If the origin table is not huge.

select distinct * from origin_table into temp_table;
truncate table origin_table;
insert into origin_table select * from temp_table ;
drop table temp_table;

edited Mar 28 '11 at 15:44

answered Mar 28 '11 at 15:38

RollingBoy

2,767
1
14
8

score 0 · Answer 3 · answered Mar 28 '11 at 15:39

0

insert into table_new
   Select col1, col2, min(pk) as pk from table_old
   group by col1, col2

-- debug table_new

drop table_old

answered Mar 28 '11 at 15:39

Beth

9,531
1
24
43

sql server 2008 deleting duplicates

3 Answers3