Updating the values in a column based on existing values

Hi,

I would like to be able to update fields in a table and I believe that the following code will make the changes but I am not sure how to code the update to apply it to the table. Value1 forms part of a primary key

      DECLARE @NewCheckDigit INT
      DECLARE @Value1 VARCHAR(14)

      IF SUBSTRING(@Value1 ,3,1) = '9'
            BEGIN
                  SET @et_Processed = SUBSTRING(@Value1 ,3,11)
            END;
      ELSE
            BEGIN
                  SET @et_Processed = SUBSTRING(@Value1 ,3,6) + RIGHT(@Value1 ,5);
            END;

      SET @NewCheckDigit = (CONVERT(BIGINT,@et_Processed) % 7) + 1

      IF SUBSTRING(@Value1 , 3,1) = '9'
            BEGIN
                  SET @Value1  = @Value1  + @NewCheckDigit
            END;
      ELSE
            BEGIN
                  SET @Value1  = STUFF(@Value1 ,9,1,@NewCheckDigit)
END:

Any help would be appreciated
Thanks
Morpheus7Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
So, after you set the variable values you need now to run the update statement.
Without more information on table name and columns that you want to update and even with no criteria, I'll leave here an example of the update command:

UPDATE YourTableNameHere
SET Processed =  @et_Processed,
        Value1 = @Value1 
WHERE YourCriteriaHere

Open in new window

0
Morpheus7Author Commented:
Hi,

Many thanks for the reply. Apologies, I left off one variable which is
 DECLARE @et_processed varchar(11)
The code that I hope will do the update is conditional on which digit is in a certain position in the string.
Value1 would be something like this AB9235678964.
Value1 is one half of a compound primary key, the other half is ImportID.
My problem is that I am unsure of how to apply the code above to the table to make the changes, ie the syntax for the update statement, bearing in mind the conditional nature of the code above.
I hope this makes it a bit clearer
Many thanks
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Still not clear for me but I already gave you the UPDATE syntax.
Your piece of code works? If so you just need to add the UPDATE statement in the end of your code.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Morpheus7Author Commented:
Hi,
Thanks for the reply,
The issue I am having is that I need to be able to update more than one row.
0
Anthony PerkinsCommented:
Is there any chance you can show all your code?  As it stands, all we can do is guess.
0
Morpheus7Author Commented:
Hi,
Thanks for replying. The code above in the original post is taken from a function used to check the validity of a serial number. From the original serial number we receive, we generate additional serial numbers based on the number of the product the customer buys. These are loaded into the table. We then need to generate validity digits and update the newly generated serial numbers with the check digit. So the code above is complete. I have modified the code from checking the validity digit to modifying it.
I hope this helps
Thanks
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Morpheus7, is not the algorithm that we are interested on. But the UPDATE statement.

You said "The issue I am having is that I need to be able to update more than one row". So which rows to you actually need to be updated?

As I posted before, you just need to work on the search criteria in WHERE clause but if you don't give us more information I'm afraid that we can't help you further.
0
Morpheus7Author Commented:
Hi,
Sorry for misunderstanding. Value1 field is the one to update
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok. It's only Value1 that need to be updated.
And how many records are we talking about? All from the table or just some? If just some, how can we know which those rows are?

For now your update statement should be similar to the following one:
UPDATE YourTableNameHere
SET Value1 = @Value1 
WHERE YourCriteriaHere

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.