We help IT Professionals succeed at work.

Updating the values in a column based on existing values

153 Views
Last Modified: 2014-09-03
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
Comment
Watch Question

Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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

Author

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
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.

Author

Commented:
Hi,
Thanks for the reply,
The issue I am having is that I need to be able to update more than one row.
CERTIFIED EXPERT
Top Expert 2012

Commented:
Is there any chance you can show all your code?  As it stands, all we can do is guess.

Author

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
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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.

Author

Commented:
Hi,
Sorry for misunderstanding. Value1 field is the one to update
IT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.