Deleting duplicated data in SQL Server

Question

I found a lot of duplicated data in my SQL Server with this query

SELECT
    [MærkeID], [Model], [Årgang], [Motor Type], [Krydsmålet],
    [Centerhul], [Bolter], [Dæk], [Fælge], [ET], [vendor], COUNT(*)
FROM
    Data.Hjuldata   
GROUP BY
    [MærkeID], [Model], [Årgang], [Motor Type], [Krydsmålet],
    [Centerhul], [Bolter], [Dæk], [Fælge], [ET], [vendor]
HAVING 
    COUNT(*) > 1

Is there a way to delete them so there is only 1 record instead of 2 of the same?

not sure what you mean with criteria but i want it so there is just 1 of each record instead of 2 of the exact same as you can see in my no column name — omini data
– omini data, Commented Jun 18, 2016 at 21:30

M.Ali · Accepted Answer · 2016-06-18 21:32:57Z

2

WITH X AS (
SELECT
     *
     ,ROW_NUMBER() OVER (PARTITION BY 
                           [MærkeID]
                          ,[Model]
                          ,[Årgang]
                          ,[Motor Type]
                          ,[Krydsmålet]
                          ,[Centerhul]
                          ,[Bolter]
                          ,[Dæk]
                          ,[Fælge]
                          ,[ET]
                          ,[vendor] ORDER BY (SELECT NULL)) RN
FROM Data.Hjuldata )
DELETE FROM X 
WHERE RN > 1

answered Jun 18, 2016 at 21:32

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Denn Over a year ago

I was almost there :(

Collectives™ on Stack Overflow

Deleting duplicated data in SQL Server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related