[dba-SQLServer] SPAM-LOW: Re: Is there a better way

OK, that was very quick air code with lots of bugs.  This one is tested and actually works:

Update MyTbl 
 Set AddrValid =
   (CASE 
        WHEN (ANK IS NOT NULL)  THEN 'ANK'
        WHEN (AddrValid IS NULL) AND (Addr Like 'PO%')  THEN 'PO'
        WHEN (AddrValid IS NULL) AND (Addr Like 'MOVED%') THEN 'MOV'
   END)

It works like a VBA "Select Case" in that once a condition is met, it drops straight through to 
the END

-- 
Stuart


On 4 Jul 2009 at 10:22, jwcolby wrote:

> Yes, I guess so.  Thanks for that suggestion, it is much simpler.  Does the next case only execute 
> if the first case fails?  Or does the (addrValid is null) pick up the fact that previous cases set 
> AddrValid?
> 
> This syntax is nothing like VBA and so I am completely in the dark as to how to use it.
> 
> John W. Colby
> www.ColbyConsulting.com
> 
> 
> Stuart McLachlan wrote:
> > Can't you use something like:
> > 
> > Update MyTbl 
> > Set AddrValid =
> > (CASE WHEN (ANK IS NOT NULL  THEN 'ANK'
> > CASE WHEN (AddrValid IS NULL) AND (Addr Like 'PO%')  THEN 'PO'
> > CASE WHEN (AddrValid IS NULL) AND (Addr Like 'MOVED%') THEN 'MOV'
> > CASE WHEN (AddrValid IS NULL) AND (Addr Like '.........%') THEN '...'
> > )
> > 
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>