How do I update duplicate value instances in C#

Question

0.00/5 (No votes)

See more:

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:

SQL

SELECT agent_shortname, COUNT(agent_shortname) AS CountOf
    FROM            BrokerTest
    WHERE        term = 'NB'
    GROUP BY agent_shortname

Posted 16-Sep-19 0:42am

Tshumore

Updated 26-Sep-19 20:54pm

Maciej Los

v2

Add a Solution

Comments

CHill60 16-Sep-19 7:29am

You absolutely do not need to loop through anything. SQL is set base so just choose an appropriate WHERE clause

W∴ Balboos, GHB 16-Sep-19 11:20am

Just a thought based upon how you wish to update the multiple-duplicate instances:

You would be creating a new set (smaller by 1) of duplicates. How do you propose to handle that, if at all? At the least, you'd need to run your procedure multiple times to get through this. Alternatively, you can serialize the duplicates.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sagan Internet Marketing · Answer 1 · 2019-09-26T20:38:00

This is untested but something like this should work

SQL

DECLARE @counter int;
SET @counter = 0;

SELECT ([agent_shortname] +
  (SELECT CASE
     WHEN CHARINDEX('CR', [agent_shortname]) > -1 THEN 'AG'
     WHEN CHARINDEX('BR', [agent_shortname]) > -1 THEN 'BR'
     ELSE 'UNKNOWN'
   END) +
  (SELECT LPAD((SELECT @counter=@counter + 1), 5, '0'))) AS [CountOf]
FROM [BrokerTest] WHERE [agent_shortname] IN
  (SELECT [agent_shortname]
     FROM [BrokerTest]
       GROUP BY [agent_shortname]
       HAVING COUNT(*) > 1)