CODEWITHSUNDEEP
sqlsql-server-2008-r2duplicates
user609511
user609511

Reputation: 4261

How can I delete duplicated row

I am using SQL Server 2008 R2.

I found duplicate rows with this script:

SELECT CLDest, CdClient,
 COUNT(CLDest) AS NumOccurrences
FROM DEST
GROUP BY CLDest,CdClient
HAVING ( COUNT(CLDest) > 1 )

It return 48 entries

Before I delete I have to make sure that I delete the doubles:

SELECT DEST.CdClient
      ,DEST.CLDest
FROM [Soft8Exp_Client_WEB].[dbo].[DEST]
WHERE DEST.CdClient IN (SELECT  CdClient
                        FROM DEST
                        GROUP BY CdClient
                        HAVING (COUNT(CLDest) > 1) )
  AND DEST.CLDest IN (SELECT CLDest
                      FROM DEST
                      GROUP BY CLDest
                      HAVING (COUNT(CLDest) > 1) )

This query returns 64628 entries

So I suppose my select is wrong.

Upvotes: 0

Views: 75

Answers (3)

ahPo
ahPo

Reputation: 384

SELECT DEST.CdClient,DEST.CLDest 
FROM [Soft8Exp_Client_WEB].[dbo].[DEST]
WHERE DEST.CdClient+DEST.CLDest 
  IN (
    SELECT CdClient+CLDest FROM DEST GROUP BY CLDest HAVING ( COUNT(CLDest) > 1 )
   )

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

SQL Server has the nice property of updatable CTEs. When combined with the function row_number(), this does what you want:

with todelete as (
      select d.*,
             row_number() over (partition by CLDest, CdClient order by newid()) as seqnum
      from dest d
     )
delete from todelete
    where seqnum > 1;

This version will randomly delete one of the duplicates. What it does is assign a sequential number to the rows with the same value and delete all but the first one found. If you want to keep something by date, then use a different expression in the order by.

Upvotes: 2

M.Ali
M.Ali

Reputation: 69494

;WITH Duplicates
 AS
   (
   SELECT CLDest
        , CdClient
        , ROW_NUMBER() OVER (PARTITION BY CLDest, CdClient ORDER BY CdClient) AS Rn
   FROM DEST
   )
DELETE FROM Duplicates
WHERE RN > 1

Upvotes: 1

Related Questions