asked on

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?

Rey Obrero (Capricorn1)

you can use something like this

UPDATE TableNAme SET TableName.Duration = Replace(Replace([Duration],"years",""),"year","")

shanesuebsahakarn

What sort of other things do you want to look for?

Jbryant22

ASKER

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?

shanesuebsahakarn

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.

Jbryant22

ASKER

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?

ASKER CERTIFIED SOLUTION

shanesuebsahakarn

membership

This solution is only available to members.

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

Start Free Trial

Jbryant22

ASKER

cool thanks for the help