Updating values, how to make it work? 1

[belcom125]

I am using the code below to update about 5000 customer records to a new customer number format. Example: ABC001, ABC002, ABD001, ABD002 and so on... first 3 are chars of 3 first chars of the customer name and last 3 is incrementing number. The works great if I were to update all customer number at once and never get back to it. However about 2000 of those records were already changed manually. When this script is used I get duplicate values because some of the numbers that the script is trying to produce already exist. I'd like to change all but management wants to leave all manually updated number as it is. How can I avoid creating all this mess and have no duplicate values in the end and so I can run this script every once in a while to update whichever record have that don't follow that convention?



BEGIN TRANSACTION

UPDATE CustomersTry
SET CustomerNumber =
CASE WHEN CustomerName LIKE 'the %' THEN SUBSTRING(CustomerName, 5, 3)
WHEN CustomerName LIKE '%retire%' THEN SUBSTRING(CustomerName,10,3)
ELSE LEFT(CustomerName, 3) END

+ RIGHT('000' + (SELECT CAST(COUNT(*) AS varchar)

FROM CustomersTry

WHERE CASE WHEN CustomerName LIKE 'the %' THEN SUBSTRING(CustomerName, 5, 3)
ELSE LEFT(CustomerName, 3) END =

CASE WHEN Cust.CustomerName LIKE 'the %' THEN SUBSTRING(Cust.CustomerName, 5, 3)
ELSE LEFT(Cust.CustomerName, 3) END

AND CustomerNumber <= Cust.CustomerNumber), 3)

FROM CustomersTry Cust

SELECT * FROM CustomersTry
Order by CustomerName,CustomerNumber

 

[mkal]

What do the values look like for CustomerNumber that haven't been updated? The reason I am asking is if the CustomerNumber is NULL or an empty set or some other value that is easily isolated you could use a where clause in your update statement to update just those rows.

 

[belcom125]

I do use where clause, I didn't copy it over here for some reason. But the thing is that when I exclude those that are already in the proper form and then run the script it still creates duplicate number due to wrong count in the select statement that is trying to determine which number is next. The count is incrorrect and then it concatinates that "incremented" and incorrect value to the string and updates the customer record with it. But the thing is that there is only about 400 of them that are duplicated no more than twice. So basically I think I need to address the problem with another script that runs after the first one that will update/increment the ones that are duplicate to the next value and leave the first one alone. I need help to figure out the logic to do this.

BEGIN TRANSACTION

UPDATE CustomersTry
SET CustomerNumber =
CASE WHEN CustomerName LIKE 'the %' THEN SUBSTRING(CustomerName, 5, 3)
WHEN CustomerName LIKE '%retire%' THEN SUBSTRING(CustomerName,10,3)
ELSE LEFT(CustomerName, 3) END

+ RIGHT('000' + (SELECT CAST(COUNT(*) AS varchar)

FROM CustomersTry

WHERE CASE WHEN CustomerName LIKE 'the %' THEN SUBSTRING(CustomerName, 5, 3)
ELSE LEFT(CustomerName, 3) END =

CASE WHEN Cust.CustomerName LIKE 'the %' THEN SUBSTRING(Cust.CustomerName, 5, 3)
ELSE LEFT(Cust.CustomerName, 3) END

AND CustomerNumber <= Cust.CustomerNumber), 3)

FROM CustomersTry Cust

WHERE CustomerNumber NOT LIKE '%retire%'
AND customernumber NOT LIKE '%-%'
AND CustomerNumber NOT LIKE '[a-z]'+'[a-z]'+'[a-z]'+'[0-9]'+'[0-9]'+'[0-9]'

SELECT * FROM CustomersTry
Order by CustomerName,Custom

erNumber

 

[sunila7]

Would some thing like this work for you?

I am assuming that the codes that have been manually modified conforms to the new code pattern and also that there are no gaps in these manually changed code

UPDATE #c set code =
CASE WHEN left(code,3)<>left(cn,3) THEN
CASE WHEN cn LIKE 'the %' THEN SUBSTRING(cn, 5, 3)
           WHEN cn LIKE '%retire%' THEN SUBSTRING(cn,10,3)
           ELSE LEFT(cn, 3) END + 
	   RIGHT('000' + CAST((SELECT COUNT(*)  FROM #c c1
	   WHERE
	   c1.code <= Cust.code and left(code,3)<>left(cn,3)) + (SELECT COUNT(*)  FROM #c c1
	   WHERE
	   left(code,3)=left(cn,3)) AS varchar), 3 )       
ELSE CODE END
FROM #c Cust

Sunil

 

[belcom125]

Nope, I tried and got a bunch of weird numbers instead of proper format CustomerNumbers and still get 30 records that occur twice.

 

[sunila7]

Can you give us some sample data (with manually changed codes) and expected output?

Sunil