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_Proces
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
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_Proces
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
Microsoft SQL ServerMicrosoft SQL Server 2005
Last Comment
Vitor Montalvão
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
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
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.
Your piece of code works? If so you just need to add the UPDATE statement in the end of your code.
ASKER
Hi,
Thanks for the reply,
The issue I am having is that I need to be able to update more than one row.
Thanks for the reply,
The issue I am having is that I need to be able to update more than one row.
Is there any chance you can show all your code? As it stands, all we can do is guess.
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
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
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.
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.
ASKER
Hi,
Sorry for misunderstanding. Value1 field is the one to update
Sorry for misunderstanding. Value1 field is the one to update
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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:
Open in new window