Geek asks and answers

Delete Duplicate Rows in SQL Server 2010

I made a mistake in the bulk insert script, so now I have "duplicates" of lines with different colX. I need to remove these duplicate lines, but I cannot figure out how to do this. 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 save the first occurrence of each (row, colX):

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

Thank you for your responses:)

+5
source share
7 answers

Try the simplest approach with Sql Server 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');

Decision:

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;

Conclusion:

| 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 maybe 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');

Decision:

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;

Conclusion:

| 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 |
+10
source

CTE , . , : MSDN

+2

, colX, :

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)

, , . , .

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);

SQLFiddle.

+2

, colX ( , " colX" ), :

;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

(, "" )

colX , uniqueidentifier, , , colX.

+1

, 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;
0

:  , emp_dept (empid, deptid), ,   Oracle

  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 )

sql , id , , .  , nid

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


  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 deletes all rows for which other rows exist that have similar kernel values ​​but smaller rowid or identity. Therefore, if duplicate rows exist, then one that has a higher row identifier or identifier will be deleted. and for the row there are no duplicates that cannot find the lower row identifier, therefore they will not be deleted.

0
source

Try this bt code at 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 () - 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;
0
source

More articles:


All Articles