I have the following sql
[code]
merge into [dbo].[BNYUKWorkingDataloadFile1] tb
using (
SELECT pk_id,[MasterIdentifier],[FundCode],
CAST([MasterIdentifier]as varchar(10))+'_'+
CAST(ROW_NUMBER() OVER (PARTITION BY[MasterIdentifier],[Fundcode]
ORDER BY [pk_id]asc) as varchar(10))
AS RowNumber
FROM [dbo].[BNYUKWorkingDataloadFile1]
where [MasterIdentifier] in (
SELECT [MasterIdentifier] FROM [dbo].[BNYUKWorkingDataloadFile1]
GROUP BY [MasterIdentifier] HAVING ( COUNT([MasterIdentifier]) > 1 ))
) t on (t.pk_id = tb.pk_id)
when matched then update
set [MasterIdentifier] = t.RowNumber;
[\code]
It gets dupicate master id for the same fund code.
I think this will work, but not usable sample data so I can't test it:
merge into [dbo].[BNYUKWorkingDataloadFile1] tb
using (
SELECT pk_id,[MasterIdentifier],[FundCode],
ROW_NUMBER() OVER (PARTITION BY[MasterIdentifier],[Fundcode]
ORDER BY [pk_id]asc) - 1 AS RowNumber
FROM [dbo].[BNYUKWorkingDataloadFile1]
where [MasterIdentifier] in (
SELECT [MasterIdentifier] FROM [dbo].[BNYUKWorkingDataloadFile1]
GROUP BY [MasterIdentifier] HAVING ( COUNT([MasterIdentifier]) > 1 ))
) t on (t.pk_id = tb.pk_id)
when matched then update
set [MasterIdentifier] = t.[MasterIdentifier] +
case when RowNumber = 0 then '' else '_' + CAST(RowNumber AS varchar(10)) end;
i tried to do this with sample data ... if it helps great
drop create sample data ...
drop table #data
go
create table #data
(
FundCode int,
MasterIdentifier varchar(100)
)
go
insert into #data select 262050,'CABVSS671'
insert into #data select 297950,'CABVSS671'
insert into #data select 297950,'CABVSS671'
insert into #data select 297950,'CABVSS671'
go
SQL .....
;WITH cte
AS (SELECT Row_number()
OVER(
partition BY fundcode, masteridentifier
ORDER BY fundcode, masteridentifier) - 1 AS rn,
*
FROM #data)
SELECT fundcode,
CASE
WHEN rn <> 0 THEN masteridentifier + '_' + Cast(rn AS VARCHAR)
ELSE masteridentifier
END
FROM cte
go
