I am looking for a function such that for each instance of duplicates found, update all values in the instance(to a new code).
The instance will be updated based on whether it contains a certain substring. Duplicates containing the substring -CO- will be updated as follows:
If the last used update code on the previous batch was AG00021 then the first duplicates instance found in current batch will be updated as :
ALID-HEA-CO-001 -- AG00022
ALID-HEA-CO-001 -- AG00022
This duplicates instance contains two rows.
Suppose their is another duplicates instance EMIL-MTR-CO-012 found with 3 rows then all three rows will be updated as :
EMIL-MTR-CO-012 -- AG00023
EMIL-MTR-CO-012 -- AG00023
EMIL-MTR-CO-012 -- AG00023
Duplicates containing substring -BR- will be updated in the same manner but using update code BR*****.. e.g in current batch if we have a duplicates instance CARE-HEA-BR-006 with 2 rows and last update code was BR00079 then result would be :
CARE-HEA-BR-006 --BR00080
CARE-HEA-BR-006 --BR00080
.
Any suggestions on how i can loop through each value in a duplicate instance and update accordingly.
I would want to achieve this using parameterized SQL queries and not LINQ.
I already have logic to get the last update code and i can successfully get it in my code.
What I have tried:
For now i can only get the total count in each duplicate instance using T-SQL in SSMS, but then i need to loop through each value, for each duplicate instance found and update. My t-sql query is:
SELECT agent_shortname, COUNT(agent_shortname) AS CountOf
FROM BrokerTest
WHERE term = 'NB'
GROUP BY agent_shortname