best way to delete duplicates

[simian336]

I have a table like below... what is the best way to delete duplicates on 2 rows?

Th_Name PT_ID
ADVNFX300/2 3
ADVNFX300/2 3
ADVNFX300/2 18
ADVNFX300/2 18
ADVNFX300/2 18
ADVNFX300/2A 18
ADVNFX300/3 3
ADVNFX300/3 3
ADVNFX300/3 18
ADVNFX300/3 18
ADVNFX300/3 18
ADVNFX300/3A 18

Expected results

Th_Name PT_ID
ADVNFX300/2 3
ADVNFX300/2 18
ADVNFX300/2A 18
ADVNFX300/3 3
ADVNFX300/3 18
ADVNFX300/3A 18

Thanks

Simi

 

[bborissov]

The best way is not to allow duplicates

SELECT DISTINCT * INTO #Test FROM YourTable

DELETE FROM YourTable

INSERT INTO YourTable
SELECT * FROM #Test

DROP TABLE #Test

NOT TESTED!!!
Make sure you have a good backup before trying this code.




Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[markros]

If you insist on deleting dups, try

;with Dups as (select *, row_number() over (partition by The_Name, Pt_ID order by The_Name) as Row from MyTable)

delete from Dups where Row > 1

PluralSight Learning Library