asked on

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

Vitor Montalvão

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

Morpheus7

ASKER

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ão

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.

Morpheus7

ASKER

Hi,
Thanks for the reply,
The issue I am having is that I need to be able to update more than one row.

Anthony Perkins

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

Morpheus7

ASKER

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ão

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.

Morpheus7

ASKER

Hi,
Sorry for misunderstanding. Value1 field is the one to update

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial