Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 301
  • Last Modified:

Urgent --Multiple updates with one Query on the same column - either SP or Query

i have a column that is auto populated daily of type varchar.  I would like a stored procedure that I could execute nightly that would go through the table and update it accordingly.

The table consists of values like "1 year", "2 years", ect.  I want to strip the year(s) off of the table as well as look for some other things.

Is this possible to do in a simple query or stored procedure?
0
Jbryant22
Asked:
Jbryant22
  • 3
  • 3
1 Solution
 
Rey Obrero (Capricorn1)Commented:
you can use something like this

UPDATE TableNAme SET TableName.Duration = Replace(Replace([Duration],"years",""),"year","")
0
 
shanesuebsahakarnCommented:
What sort of other things do you want to look for?
0
 
Jbryant22Author Commented:
the table contains auto, none, and other values considered invalid so I want to set those to 0.  So the query above will just leave me with the number?
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
shanesuebsahakarnCommented:
Hmm - something like this, I think:

UPDATE MyTable SET MyField=Str$(Val([MyField]))

should do it. This will update the field to the number if it starts with a number - otherwise, it updates it to 0.
0
 
Jbryant22Author Commented:
oops, I forgot one thing.  the column also contains either Auto or other.  I have to treat these differently and I have to leave those alone.  I deal with them in the asp code.  so i could do a where myfield <> "other" or myfield <>"auto".  right?
0
 
shanesuebsahakarnCommented:
Something like this in that case:

UPDATE MyTable SET MyField=IIf([MyField]='Auto' Or [MyField]='Other',[MyField],Str$(Val([MyField])))

I *think* that will work from asp.
0
 
Jbryant22Author Commented:
cool thanks for the help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now