hi,
i have the follwoing code that adds _1,_2 etc for every master id where it finds a dupulicate.
[code]
merge into dbo.SSCIREWorkingDataloadFile3 tb
using (
SELECT PK_ID,SecurityCaptionDescription15,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.SSCIREWorkingDataloadFile3
where MasterIdentifier in (
SELECT MasterIdentifier FROM dbo.SSCIREWorkingDataloadFile3
GROUP BY MasterIdentifier HAVING ( COUNT(MasterIdentifier) > 1 ))
AND UPPER(SecurityCaptionDescription15) ='REPO' OR UPPER(SecurityCaptionDescription15) ='REPOS'
and fundcode= fundcode
) t on (t.PK_ID = tb.PK_ID)
when matched then update
set MasterIdentifier = t.RowNumber;
[\code]
it does work to a point. i need it to just add the _1 _2 etc based on master id and fundcode. at the moment it just add the numbers to every master id regardless of fund code. any idea how i can get it to do it on masterid and fund code.