Set a unique value for the first duplicate, and a default value for subsequent duplicates of the same number

Question

For each PAN number duplicates, set a specific REMARKS value for the first duplicate and a default value for all subsequent duplicates.

Here is the expected output

PAN	REMARKS
11211	The One
11211	0
11211	0
11211	0
13111	The One
13111	0
13111	0
13111	0

Here are some queries I tried but they didn't work.

// First Query
UPDATE YourTableName
SET REMARKS = CASE 
                 WHEN (
                    SELECT COUNT(*)
                    FROM YourTableName AS T2
                    WHERE T2.PAN = YourTableName.PAN AND T2.PAN <> ''
                 ) = 0 THEN 'The One'
                 ELSE '0'
             END;

// second query
WITH CTE AS (
    SELECT PAN, ROW_NUMBER() OVER (PARTITION BY PAN ORDER BY (SELECT 0)) AS RowNum
    FROM YourTableName
)
UPDATE YourTableName
SET REMARKS = CASE WHEN CTE.RowNum = 1 THEN 'The One' ELSE '0' END
FROM YourTableName 
INNER JOIN CTE ON YourTableName.PAN = CTE.PAN

//Third Query
UPDATE x
SET x.Total_Outstanding = '0',x.Total_exposure='0'
FROM (
      SELECT ROW_NUMBER() OVER (
      PARTITION BY PAN_NO
      ORDER BY PAN_NO
   ) row_num,
   [Family_Name]
      ,[Client_Name]
      ,[Account_Name]
      ,[Account_Id]
      ,[Held_Away]
      ,[Prospect]
      ,[Product_Name]
      ,[Asset_Name]
      ,[AMC_Short_Name]
      ,[Total_exposure]
      ,[Instrument_Category_Name]
      ,[Instrument_Name]
      ,[ISIN]
      ,[BOS_Code]
      ,[Total_Outstanding]
      ,[PAN_NO]
      ,[Folio]
      ,[Concatenate1]
      ,[Units]
      FROM dtpandata
      ) x where x.row_num > 1

Answer 1

You can use CTE and WINDOW FUNCTION(LAG,count) to solve the problem

;WITH CTE AS (
    select REMARKS
            ,LAG(PAN,1) over (partition by PAN ORDER BY (SELECT 0)) as lPAN
            ,count(*) over (partition by PAN ORDER BY (SELECT 0)) as  countPAN
            from Tn
)
UPDATE T
SET REMARKS = case when lPAN is null and countPAN>1 then 'The One' else '0'   end
FROM CTE  T

You can create insert base data with the following statements:

drop table if exists Tn

create table Tn(ID int,PAN  int,Client_Name varchar(100),REMARKS  varchar(100))

insert into Tn (Id,PAN,Client_Name) values( 1,11211,'aa1')  
insert into Tn (Id,PAN,Client_Name) values(2,11211,'aa2')       
insert into Tn (Id,PAN,Client_Name) values(3,11211,'aa3')       
insert into Tn (Id,PAN,Client_Name) values(4,11211,'aa4')       
insert into Tn (Id,PAN,Client_Name) values(5,13111,'aa4')       
insert into Tn (Id,PAN,Client_Name) values(6,13111,'aa5')       
insert into Tn (Id,PAN,Client_Name) values(7,13111,'aa5')       
insert into Tn (Id,PAN,Client_Name) values(8,13111,'aa6')