CODEWITHSUNDEEP
sql-server
anil21st
anil21st

Reputation: 9

How to Remove Duplicate Statement

How to delete duplicate data row in SQL Server where there are not any unique value differences? I remain only one statement from my sales table (dbo.Sales)

ID  DESCRIPTIONS QTY RATE AMOUNT
--------------------------------
1   APPLE         50  100 1000 
1   APPLE         50  100 1000 
1   APPLE         50  100 1000 
1   APPLE         50  100 1000

Upvotes: 0

Views: 52

Answers (4)

Ravi
Ravi

Reputation: 1172

I suggest to add a column like rn and feed it by row_number() over (Partition by ID, DESCRIPTIONS ,QTY, RATE, AMOUNT order by Id)

Now delete the data having rn not equal to 1

after completion drop that column... this is a one time solution if it is frequent that add a unique key in your table

Upvotes: 0

saman tr
saman tr

Reputation: 94

you can use this: 

select distinct * into temp from tableName
delete from tableName
insert into tableName
select * from temp
drop table temp

Upvotes: 0

PSK
PSK

Reputation: 17943

You can delete like following.

DELETE A 
FROM   (SELECT Row_number() 
                 OVER ( 
                   partition BY id, descriptions, qty, rate, amount 
                   ORDER BY (SELECT 1)) AS rn 
        FROM   table1) A 
WHERE  a.rn > 1

If you want to use CTE, you can try like following.

;WITH cte 
     AS (SELECT Row_number() 
                  OVER( 
                    partition BY id, descriptions, qty, rate, amount 
                    ORDER BY (SELECT 1)) RN 
         FROM   table1) 
DELETE FROM cte 
WHERE  rn > 1

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521259

We can try using a CTE here to arbitrarily delete all but one of the duplicates:

WITH cte AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY ID, DESCRIPTIONS, QTY, RATE, AMOUNT
            ORDER BY (SELECT NULL)) rn
    FROM yourTable
)

DELETE
FROM cte
WHERE rn > 1;

Upvotes: 1

Related Questions