[dba-SQLServer] Is there a better way

Take a look at CASE WHEN in the BOL.

Doris Manning
Database Administrator
Hargrove Inc. 

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Saturday, July 04, 2009 8:41 AM
To: Dba-Sqlserver
Subject: [dba-SQLServer] Is there a better way

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