delete duplicates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

delete duplicates

Author

Topic

mrki_81
Starting Member

4 Posts

Posted - 2012-01-02 : 04:16:41

hi all,

I have a table structure like this

Nr Name Code
------------------------
111 Kuchen DE
111 Kuchen AT
111 Obst OP
122 Stein OP
132 Kuchen AT
132 Glas OP

In the line 1 and 2 there are almost the same entries. they are only different in the code. I need to cleanup this. All entries with the same Nr and Name have to be merged together and get the Code "DE"

there are 3 types of code: DE, AT and OP. OP entries are always unique, so there is no problem with them. Entries with the code DE and AT can be duplicates. If there is a duplicate like in the line 1 and two, the line with the Code AT should be not selected.

I have already worked on this and I can select the duplicates and clear them. Here is the code:

select Nr, Name, 'De'
from (
    select  Nr, Name, Code
  	from Database_All
 	where Code <> '--' and (Code = 'DE' or Code='AT')
 	group by 1,2,3
    ) as temp
 group by 1,2
 having count(*) > 1

I need now the rest. The values that have no duplicates. Then I can merge this 2 results

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-01-02 : 05:11:19

Should be this:

delete t1
from table as t1 where Code = 'AT' and exists(select * from table as t2 where t2.Nr=t1.Nr and t2.Name=t1.Name and t2.Code='DE')

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-02 : 12:18:33

[code]
DELETE t
FROM (SELECT COUNT(1) OVER (PARTITION BY Nr, Name) AS Cnt,Code
FROM table
WHERE Code <> 'OP'
)t
WHERE Cnt >1
AND Code <> 'DE'
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources