[dba-SQLServer] Is there a better way

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 '...'
)

-- 
Stuart


On 4 Jul 2009 at 8:40, jwcolby wrote:

> I am updating a flag field which tells me that an address is valid.  The code currently consists of 
> a series of about FIVE passes through the table doing things like:
> 
> Update MyTbl
> SET AddrValid = 'ANK'
> WHERE ANK_ IS NOT NULL
> 
> Update MyTbl
> SET AddrValid = 'PO'
> WHERE (AddrValid IS NULL) AND (Addr Like 'PO%')
> 
> 
> Update MyTbl
> SET AddrValid = 'MOV'
> WHERE (AddrValid IS NULL) AND (Addr Like 'MOVED%')
> 
> Etc next code
> 
> Etc Next code
> 
> My objective is to have a moved flag that I can find all of my ANK records, or EXCLUDE the ANK and 
> Moved etc.
> 
> Obviously I have a ton of processing going on here.  It has to do N passes through the table to find 
> and create N different codes.  With tens of millions of records this is taking a large while.
> 
> Is there a faster way?  Drop the where and put the where processing into a UDF that figures out what 
> the code should be.  So every record gets processed, and the UDF takes the fields in the where 
> clause and decides what code to assign.
> 
> Something like that.  Would it be faster?  Given that we are by design visiting every row I would 
> think it would be, though it might be a complex function to design.
> 
> 
> 
> -- 
> John W. Colby
> www.ColbyConsulting.com
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>