August 1, 2017 at 1:57 pm
I am looking for help with a script please. New to SQL.
I have a database with a cutomer number in one of the fields. Lets call the field MISCCODE.
I need to add a "1" (the number, one) to all these customer numbers in field MISCCODE.
Before I do that I must check if the field is blank, so as not to add a "1" and nothing else.
If blank then skip.
There are 66,000 entries to replace or update.
Would appreciate some help.
August 1, 2017 at 2:50 pm
Without seeing the data, I would try something like:BEGIN TRANSACTION;
SELECT MISCCODE FROM table;
UPDATE table
SET MISCCODE = MISCCODE + '1'
WHERE MISCCODE NOT LIKE ''
AND MISCCODE IS NOT NULL;
SELECT MISCCODE FROM table;
ROLLBACK TRANSACTION;
To explain the code - BEGIN TRANSACTION puts everything in as 1 command and runs through it without actually making changes to the table.
The first SELECT will show you the current value in the table
Next is the UPDATE which will change the data
Next is the final SELECT which will show you the changed data so you can compare and ensure it is correct
And finally the ROLLBACK will undo the changes.
I recommend doing the ROLLBACK as above so you can test making the changes to ensure that things update the way you want them to.
If things look good, change the last line to "COMMIT TRANSACTION" instead of "ROLLBACK TRANSACTION" and the data will be updated.
The above code assumes that MISCCODE is a varchar or nvarchar data type as the '1' is a string that is being added on to the end (ie "bob" would become "bob1"). If you want to increment it by 1 (ie it is 100 and you want 101), remove the appostrophe around the 1 (ie SET MISCCODE = MISCCODE + 1).
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 1, 2017 at 2:54 pm
alex-1124258 - Tuesday, August 1, 2017 1:57 PMI am looking for help with a script please. New to SQL.
I have a database with a cutomer number in one of the fields. Lets call the field MISCCODE.
I need to add a "1" (the number, one) to all these customer numbers in field MISCCODE.
Before I do that I must check if the field is blank, so as not to add a "1" and nothing else.
If blank then skip.
There are 66,000 entries to replace or update.Would appreciate some help.
UPDATE yourTable SET
MISCCODE = '1' + MISCCODE
WHERE
MISCCODE IS NOT NULL
AND MISCCODE <> ''
The above code assumes the MISCCODE is not a numeric type. You did not provide a table creation statement so we have no idea.
August 1, 2017 at 8:16 pm
Thank you so much.
I have 12 tables to update and have tried both these.
Appreciate your help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply