Update records only there is duplicate

Raj0125 511 Reputation points
2023-06-02T10:22:08.83+00:00

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;
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,333 questions
0 comments No comments
{count} votes

Accepted answer
  1. Rory Falla 80 Reputation points
    2023-06-02T15:21:22.8766667+00:00

    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)
    		 )
    
    1 person found this answer helpful.
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. PercyTang-MSFT 12,426 Reputation points Microsoft Vendor
    2023-06-05T02:36:45.35+00:00

    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.

    0 comments No comments

  2. Raj0125 511 Reputation points
    2023-06-05T11:14:48.7733333+00:00

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