Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
WJHamel
Aged Yak Warrior
651 Posts |
 Posted - 2012-04-16 : 14:22:32
|
Having trouble with the following. I need to set the Warrant_type field to either "F" or "M" based on a certain sequency of digits at the end of the ARRESTNO field. I'm currently using"Update arrest.dbo.arr_afss_OBTS set Warrant_type='M' where arrestno like '%74'Update arrest.dbo.arr_afss_OBTS set Warrant_type='M' where arrestno like '%743'Update arrest.dbo.arr_afss_OBTS set Warrant_type='M' where arrestno like '%2616' Problem is, what i INTEND to have happen is in the case of the first update, set Warrant_type to 'M' when the LAST TWO digits of ARRESTNO are '74'. however, as you can imagine, what it's doing is setting the warrant_type to 'M" whereever the last two digits of arrestno are '74', but the preceding characters are anything at all. Obviously, it's making a mess of things. Am i using the wrong command (like %) or is my syntax just off with the current set of commands?thanksjames |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-04-16 : 14:25:32
|
| I'm not following... So if the last 2 digits are 74, how do you determine if you should update it not? Is there some character sequence preceeding the 74 that you do not want to update? |
 |
|
|
WJHamel
Aged Yak Warrior
651 Posts |
Posted - 2012-04-16 : 14:28:31
|
| actually, i see the flaw. What i actually need to do is match against the last six digits in arrestno. Anything less than six digits is padded by zeros to make it six digits. So, in the case of 74, the value would actually be 000074, and in the case of 743, it would be 000743, and 002616 for 2616. Now, knowing that, i know how to reformat my update.thanks. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-16 : 14:36:55
|
| why do you need separate updates? in all cases target value is same. then why dont include all of them in a single update?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
vinu.vijayan
Posting Yak Master
227 Posts |
Posted - 2012-04-17 : 02:37:04
|
Yes. You can combine all the updates into 1 as follows:Update arrest.dbo.arr_afss_OBTS set Warrant_type='M' where arrestno like '%000074' OR arrestno like '%000743' OR arrestno like '%002616' N 28° 33' 11.93148"E 77° 14' 33.66384" |
|
|
|
|
|
|
|
|
Update/Set/Where/Like