Delete “duplicate” rows in SQL Server 2010

问题:

I made a mistake in a bulk insert script, so now i have "duplicate" rows with different colX. I need to delete this duplicate rows, but I cant figure out how. To be more precise, I have this:

 col1 | col2 | col3 | colX      
----+----------------------
  0   |  1   |  2   |  a
  0   |  1   |  2   |  b
  0   |  1   |  2   |  c
  0   |  1   |  2   |  a
  3   |  4   |  5   |  x
  3   |  4   |  5   |  y
  3   |  4   |  5   |  x
  3   |  4   |  5   |  z

and I want to keep the first occurrence of each (row, colX):

 col1 | col2 | col3 | colX      
----+----------------------
  0   |  1   |  2   |  a
  3   |  4   |  5   |  x

Thank you for your replies :)

回答1:

Try the simplest approach with Sql Server's CTE: http://www.sqlfiddle.com/#!3/2d386/2

Data:

CREATE TABLE tbl
    ([col1] int, [col2] int, [col3] int, [colX] varchar(1));

INSERT INTO tbl
    ([col1], [col2], [col3], [colX])
VALUES
    (0, 1, 2, 'a'),
    (0, 1, 2, 'b'),
    (0, 1, 2, 'c'),
    (0, 1, 2, 'a'),
    (3, 4, 5, 'x'),
    (3, 4, 5, 'y'),
    (3, 4, 5, 'x'),
    (3, 4, 5, 'z');

Solution:

select * from tbl;

with a as
(
  select row_number() over(partition by col1 order by col2, col3, colX) as rn 
  from tbl   
)
delete from a where rn > 1;

select * from tbl;

Output:

| COL1 | COL2 | COL3 | COLX |
-----------------------------
|    0 |    1 |    2 |    a |
|    0 |    1 |    2 |    b |
|    0 |    1 |    2 |    c |
|    0 |    1 |    2 |    a |
|    3 |    4 |    5 |    x |
|    3 |    4 |    5 |    y |
|    3 |    4 |    5 |    x |
|    3 |    4 |    5 |    z |


| COL1 | COL2 | COL3 | COLX |
-----------------------------
|    0 |    1 |    2 |    a |
|    3 |    4 |    5 |    x |

---

Or perhaps this: http://www.sqlfiddle.com/#!3/af826/1

Data:

CREATE TABLE tbl
    ([col1] int, [col2] int, [col3] int, [colX] varchar(1));

INSERT INTO tbl
    ([col1], [col2], [col3], [colX])
VALUES
    (0, 1, 2, 'a'),
    (0, 1, 2, 'b'),
    (0, 1, 2, 'c'),
    (0, 1, 2, 'a'),
    (0, 1, 3, 'a'),
    (3, 4, 5, 'x'),
    (3, 4, 5, 'y'),
    (3, 4, 5, 'x'),
    (3, 4, 5, 'z');

Solution:

select * from tbl;


with a as
(
    select row_number() over(partition by col1, col2, col3 order by colX) as rn 
    from tbl   
)
delete from a where rn > 1;

select * from tbl;

Output:

| COL1 | COL2 | COL3 | COLX |
-----------------------------
|    0 |    1 |    2 |    a |
|    0 |    1 |    2 |    b |
|    0 |    1 |    2 |    c |
|    0 |    1 |    2 |    a |
|    0 |    1 |    3 |    a |
|    3 |    4 |    5 |    x |
|    3 |    4 |    5 |    y |
|    3 |    4 |    5 |    x |
|    3 |    4 |    5 |    z |

| COL1 | COL2 | COL3 | COLX |
-----------------------------
|    0 |    1 |    2 |    a |
|    0 |    1 |    3 |    a |
|    3 |    4 |    5 |    x |

回答2:

I would suggest to use CTE and read all non-dup records in a separate table if you have many duplicates. However, there is a recommended post to follow: MSDN

回答3:

If you are OK with just keeping the minimum value of colX, you can do this:

delete t from t inner join 
    (select  min(colx) mincolx, col1, col2, col3
     from t
     group by col1, col2, col3
     having count(1) > 1) as duplicates
   on (duplicates.col1 = t.col1
   and duplicates.col2 = t.col2
   and duplicates.col3 = t.col3
   and duplicates.mincolx <> t.colx)

The problem is that you still have rows where all four columns are the same. To get rid of these, after running the first query, you then have to use a temp table.

SELECT distinct col1, col2, col3, colx 
INTO temp
  FROM (SELECT col1, col2, col3
         from t 
         group by col1, col2, col3
         having count(1) > 1) subq;

DELETE from t where exists 
   (select 1 from temp 
     where temp.col1 = t.col1 
       and temp.col2 = t.col2 
       and temp.col3 = t.col3);

Here's an example SQLFiddle.

回答4:

Assuming colX is unique (which is not the case in your example, even though you said "different colX") you could use the following to delete the duplicates:

;with cteDuplicates as
(
    select 
        *,
        row_number() over (partition by col1, col2, col3 order by colX) as ID
    from Duplicates
)
delete D from Duplicates D
    inner join cteDuplicates C on C.colX = D.Colx
where ID > 1

(Let's say your table is named "Duplicates")

If colX is not unique, add a new uniqueidentifier column, insert distinct values into it and then use the code above by joining on that column instead of colX.

回答5:

I assume you're using SQL Server 2005/2008.

SELECT col1,
       col2,
       col3,
       colx
FROM
  (SELECT *,
          row_number() OVER (PARTITION BY col1,col2,col3
                             ORDER BY colx) AS r
   FROM table_name) a
WHERE r = 1;

回答6:

Simplest solution could be as follows suppose we have table emp_dept(empid, deptid) which has duplicate rows, On Oracle database

  delete from emp_dept where exists ( select * from emp_dept i where i.empid = emp_dept.empid and i.deptid = emp_dept.deptid and i.rowid < emp_dept.rowid ) 

On sql server or anydatabase which does not support row id kinda feature , we need to add identity column just to identify each row. say we have added nid as identity to the table

alter table emp_dept add nid int identity(1,1) -- to add identity column

now query to delete duplicate could be written as

  delete from emp_dept where exists ( select * from emp_dept i where i.empid = emp_dept.empid and i.deptid = emp_dept.deptid and i.nid< emp_dept.nid ) 

Here the concept is delete all rows for which there exists other rows which have similar core values but smaller rowid or identity. Hence if there exists duplicate rows then one which has higher row id or identity will get deleted. and for row there isn't duplicate it fail in finding lower row id hence will not get deleted.

回答7:

Try this code bt on your own risk

Delete from Table_name
WHERE Table_name.%%physloc%%
      NOT IN (SELECT MAX(b.%%physloc%%)
              FROM   Table_name b
              group by Col_1,Col_2)

Second method using row_number() this is safe method

WITH CTE_Dup AS
(

 SELECT * ROW_NUMBER()OVER (PARTITIONBY SalesOrderno, ItemNo ORDER BY SalesOrderno, ItemNo)
 AS ROW_NO
 from dbo.SalesOrderDetails
)
Delete FROM CTE_Dup;