Update records only there is duplicate

Question

Hi,

I want excute below Query only for records habing duplicate S_ID .If there is only single record for S_ID i don't want to consider that record to update.

;with CTE1 as(
  select S_Id,Effective_Start_Date, Inserted_Date,Updated_date,
         row_number()over(partition by S_Id order by Effective_Start_Date desc) as rn from 
		 transac_Sample
		 where Deleted_Indicator <> 'Y' --
		 )
		 update CTE1 
		 SET Effective_Start_Date = (select max(Effective_Start_Date)
		 from CTE1 c
		 where CTE1.S_Id=c.S_Id and c.rn=2),
		 
		 Inserted_Date = (select max(Effective_Start_Date)
		 from CTE1 c
		 where CTE1.S_Id=c.S_Id and c.rn=2),

		 Updated_Date = (select max(Effective_Start_Date)
		 from CTE1 c
		 where CTE1.S_Id=c.S_Id and c.rn=2)
		 
		 where rn =1;

Answer 1

Something like this will work, though may not be practical depending on the size of transac_Sample table

SELECT count(S_Id) as COUNTA, S_Id, Effective_Start_Date, Inserted_Date, Updated_date
into #tmp
FROM transac_Sample
GROUP BY S_Id, Effective_Start_Date, Inserted_Date, Updated_date
HAVING count(S_Id) >1

select S_Id,Effective_Start_Date, Inserted_Date,Updated_date,
         row_number()over(partition by S_Id order by Effective_Start_Date desc) as rn from 
		 transac_Sample
		 where Deleted_Indicator <> 'Y' --
		 and S_Id in (SELECT S_Id from #tmp)
		 )

Answer 2

Hi @Raj0125

If the judgment is based on whether there is duplicate, it is more appropriate to use count>1.

Can you provide some sample data along with the expected output? This makes it easier for us to debug the code.

Answer 3

Thanks it working now restricted filter for count(id) >1