duplicate records with null values to be deleted

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 2005 Forums

Transact-SQL (2005)

duplicate records with null values to be deleted

Author

Topic

mummy
Starting Member

9 Posts

Posted - 2009-03-10 : 08:50:14

Hi,

I need to delete the duplicate records as like this.

column1 column2 column2 column4 column5 column6
ABC 111 XYZ 500 NULL NULL
ABC 111 XYZ 500 ABC111 XYZ414
ABC 111 XYZ 411 NULL NULL
ABC 111 AXX 454 ABC111 XYZ414
ABC 252 AXX 466 NULL NULL
ABC 252 AXX 466 DSZ121 DSS414
ABC 252 AXX 457 NULL NULL

Those records marked in blue color needs to be removed since the same record is appearing next to it with values in column5 & 6.

Key point is: all records will be same from column1 - 4 and column 5 & 6 with NULL values.

Kindly assist.

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-10 : 09:02:55

delete t from
( select row_number() over ( partition by col1,col2,col3,col4 order by col1 ) rn from table ) t
where t.rn = 1

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-10 : 09:03:58

[code]Delete t
from
(Select ROW_NUMBER() OVER(Partition by col1,col2,col3,col4 Order by col5 desc,col6 desc)as seq,* from table)t
Where t.seq >1[/code]

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-10 : 09:19:57

Just this will do....no?

delete from urtable where column5 is null and column6 is null

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-10 : 09:27:09

quote:
Originally posted by vijayisonly

Just this will do....no?

delete from urtable where column5 is null and column6 is null

NO. This will delete wherever col5 and col6 is null

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-10 : 13:35:32

quote:
Originally posted by Nageswar9

delete t from
( select row_number() over ( partition by col1,col2,col3,col4 order by col1 ) rn from table ) t
where t.rn = 1

this will delete first belonging to each col1,col2,col3,col4 group, even if column5 column6 was not null

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-10 : 13:37:31

quote:

Originally posted by sodeep

Delete t
from 
(Select ROW_NUMBER() OVER(Partition by col1,col2,col3,col4 Order by col5 desc,col6 desc)as seq,* from table)t
Where t.seq >1

same problem as earlier suggestion
this will delete first belonging to each col1,col2,col3,col4 group, even if column5 column6 was not null

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-10 : 13:40:47

[code]
DELETE t
FROM
(
SELECT column1, column2, column2, column4, column5, column6,
COUNT(1) OVER (PARTITION BY column1, column2, column2, column4) As Occurance
FROM Table
)t
WHERE column5 IS NULL
AND column6 IS NULL
AND Occurance >1
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources