Need help deleting duplicate rows with preferential condition

Let's say I have a table named Sample with the following columns:

ID - int identity(1,1)
Col1 - varchar(10)
Col2 - varchar(10)
Col3 - varchar(10)
Col4 - varchar(10)

Let's say the data in this table are as follows:

ID,Col1,Col2,Col3,Col4
1,A,B,null,null
2,A,B,C,D
3,A,B,null,null
4,B,C,null,D
5,B,C,D,E
6,B,C,A,null

I would like to remove duplicates from this table having the same Col1 and Col2 values but I would prefer to delete those rows where Col3 or Col4 or both Col3 and Col4 are null. So in the above example, I would prefer to delete the rows with ID = 1, 3, 4, and 6, and keep the rows with ID = 2 and 5.

How do I do this? Please show your transact SQL code solution.

Thanks in advance!

hi

hope this helps :slight_smile:

this solution is not about duplicates !!

please click arrow to the left for Create Sample Data
create table #temp 
(
ID  int identity(1,1)
,Col1  varchar(10)
,Col2  varchar(10)
,Col3  varchar(10)
,Col4  varchar(10)
)

insert into #temp select 'A','B',null,null
insert into #temp select 'A','B','C','D'
insert into #temp select 'A','B',null,null
insert into #temp select 'B','C',null,'D'
insert into #temp select 'B','C','D','E'
insert into #temp select 'B','C','A',null
;with cte as 
(
select distinct col1,col2 from #temp
)
delete  #temp 
 from #temp a join cte b 
on a.col1 = b.col1 and a.col2 = b.col2 and a.col3 is null or a.col4 is null

image

1 Like