March 7, 2008 at 2:20 pm
We have a table that holds tax info in this format:
TAX GROUP ID TAX GROUP DESC
VA-5049593 VA-Arlington County
VA-2452452 VA-Richmond City
VA-9438834 VA-Essex County
I want to change each taxgroup ID to be the same thing as it's tax group description.
In other words, I want to replace what is in tax_group_hdr.tax_group_id with the corresponding tax_group_hdr.tax_group_description and I only want to do it for tax_group_ID's that begin with "VA"
If anyone can give me a hand with a script to do this it would be GREATLY appreciated!!
March 7, 2008 at 2:29 pm
This looks like a job for an update statement 🙂 I do not know how your data is stored but since you say begins with VA. I will grab the first two characters for id.
UPDATE MYTABLE
SET TAX_GROUP_ID = TAX_GROUP_DESC
WHERE LEFT(TAX_GROUP_ID, 2) = 'VA'
March 7, 2008 at 2:32 pm
UPDATE tax_group_hdr
Set tax_group_id = tax_group_description
Where tax_group_id LIKE 'VA%'
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 7, 2008 at 2:43 pm
Hold the phone... how do you know that the only things that start with VA are VA- as in Virginia... never do an update of this nature without first doing SELECT using the same criteria and then using the ol' Mark I Mod I Eyeball to double check.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 7, 2008 at 2:49 pm
Hold the phone... how do you know that the only things that start with VA are VA- as in Virginia... never do an update of this nature without first doing SELECT using the same criteria and then using the ol' Mark I Mod I Eyeball to double check.
I agree. It is always good to do a select to view what you are about to update. The data input seems to be consistent. It looks like the OP did some research on the table before posting, but as a general rule of thumb, its better to be safe than sorry 😉
March 7, 2008 at 2:49 pm
Jeff Moden (3/7/2008)
Hold the phone... how do you know that the only things that start with VA are VA- as in Virginia... never do an update of this nature without first doing SELECT using the same criteria and then using the ol' Mark I Mod I Eyeball to double check.
Yup - that was the first thing I checked! I'm all clear if I use VA-
Thank you all for your help!!!
March 7, 2008 at 2:54 pm
OK, new quirk I just realized.
tax_group_ID is limited to 10 characters so if I run this it's going to give me an error.
How can this script be modified to accommodate the 10 char limit?
March 7, 2008 at 2:58 pm
UPDATE MYTABLE
SET TAX_GROUP_ID = LEFT(TAX_GROUP_DESC,10)
WHERE LEFT(TAX_GROUP_ID, 3) = 'VA-'
March 7, 2008 at 2:59 pm
You can use whichever query you want but the end result is you want to use the left function to get the first 10 characters.
March 7, 2008 at 3:00 pm
Sure, you can use LEFT 10 to do that... but stop just for a minute... you've not told us why you want to overwrite the ID with the Description. That's probably a mistake in and of itself... If you change the column width of the ID column, that could break a GUI or a view somewhere is in the app and then you're really in deep Kimchie... why do you want to overwrite the ID with the Description?
--Jeff Moden
Change is inevitable... Change for the better is not.
March 7, 2008 at 3:02 pm
I can see you running into another problem with this update. What if a city and county have the same name and the name is greater than 10 characters. This will cause the ids to be the same and should throw an error at you.
March 7, 2008 at 3:04 pm
Sure, you can use LEFT 10 to do that... but stop just for a minute... you've not tols us why you want to overwrite the ID with the Description. That's probably a mistake in and of itself... If you change the column width of the ID column, that could break a GUI or a view somewhere is in the app and then you're really in deep Kimchie... why do you want to overwrite the ID with the Description?
Posted a possible scenario that could potentially break code.
March 9, 2008 at 12:54 pm
That's a good question, let me clarify - "tax_group_id" is the display name for the tax group and can be change via the apps management gui as well. I just don't want to change the 155 "VA%" groups manually. the "tax_group_id" actually belongs to the "tax_group_uid" which is the numeric value given to that record by the system and THAT is what cannot be altered with out breaking stuff 😀
March 9, 2008 at 2:48 pm
Cool, you should be able to use the left function. I didnt want you hitting any speeds bumps, without being aware. 🙂
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply