Delete Duplicate Rows Using Sub-query

May 26, 2024 Post a Comment

I'm using SQL Server 2014 and utilizing the AdventureWorks2012 sample database provided by Microsoft. I'm trying to delete duplicate rows using sub-query below (option #2): /* Opt

Solution 1:

If you wanted to delete the duplicate name using a subquery,use the following method.

DELETE t
FROM  (SELECT  NAME,ROW_NUMBER () OVER (PARTITIONBY NAME ORDERBY NAME) AS Flag
              FROM  SQLPractice.[dbo].[CURRENCY]
            ) t
WHERE t.Flag >1
GO

You can also achieve this using common table expression (CTE).

;WITH cte_1
AS (SELECT  NAME,ROW_NUMBER () OVER (PARTITIONBY NAME ORDERBY NAME) AS Flag
              FROM  SQLPractice.[dbo].[CURRENCY]
            ) 
DELETEFROM cte_1
WHERE Flag >1

Solution 2:

One of possible methods:

DELETE tt
FROM [your table] tt
   INNERJOIN

    (SELECT NAME, MIN(PK) AS MIN_KEY)
    FROM [your table]
    GROUPBY Name
    HAVINGCOUNT(*) >1) dup ON dup.name = tt.name and tt.PK <> dup.MIN_KEY

Solution 3:

Option #2 deletes all rows because the Subquery inside EXISTS will always return rows for all the rows of the table. There must be some relation between subquery inside EXISTS and the parent query. The subquery must generate different results according to each row of the table. One option delete to duplicate rows using a subquery when table has an identity col is :

DELETEfrom SQLPractice.[dbo].[CURRENCY]
where identityCol notin ( selectmin(identityCol) FROM SQLPractice.[dbo].[CURRENCY] GROUPBY NAME)

Solution 4:

In your sample case, Row_Number() will not help you to solve your problem. Because the duplicate rows are identical even in the primary key (candidate field) which is the CurrencyCode

Since you simply insert the same row into the target table, the ModifiedDate field is also the same.

For the sample case, you can apply a solution described at delete duplicate rows where no primary key exists

You can test and see that below DELETE command will delete all rows in the table

delete [dbo].[CURRENCY]
from [dbo].[CURRENCY]
innerjoin (
    selectROW_NUMBER() over (partitionby CurrencyCode orderby ModifiedDate) rn, CurrencyCode, ModifiedDate from [dbo].[CURRENCY]
) dublicates
    on dublicates.CurrencyCode = [dbo].[CURRENCY].CurrencyCode and
       dublicates.ModifiedDate = [dbo].[CURRENCY].ModifiedDate
where dublicates.rn >1

For example from the tutorial, cursor method is suggested You can use following

DECLARE@CountintDECLARE@CurrencyCodevarchar(10)
DECLARE@ModifiedDate datetime

DECLARE dublicate_cursor CURSOR FAST_FORWARD FORSELECT CurrencyCode, ModifiedDate, Count(*) -1FROM CURRENCY
GROUPBY CurrencyCode, ModifiedDate
HAVINGCount(*) >1OPEN dublicate_cursor

FETCH NEXT FROM dublicate_cursor INTO@CurrencyCode, @ModifiedDate, @Count

WHILE @@FETCH_STATUS =0BEGINSET ROWCOUNT @CountDELETEFROM CURRENCY WHERE CurrencyCode =@CurrencyCodeAND ModifiedDate =@ModifiedDateSET ROWCOUNT 0FETCH NEXT FROM dublicate_cursor INTO@CurrencyCode, @ModifiedDate, @CountENDCLOSE dublicate_cursor
DEALLOCATE dublicate_cursor

Solution 5:

With statement remove only duplicate rows because it collect all duplicate records and then perform delete operation.

While in your sub-query you haven't specify where condition on which records you wants to delete, it should be written as below:

DELETE SQLPractice.[dbo].[CURRENCY]
WHEREEXISTS  
(
    SELECT*FROM 
    (
        SELECT 
        NAME,
        ID,
        ROW_NUMBER () OVER (PARTITIONBY NAME ORDERBY NAME) AS Flag
        FROM SQLPractice.[dbo].[CURRENCY] 
    )   AS T
    WHERE Flag >1AND T.ID=[CURRENCY].ID
)

Lab Sql