Re: CASE Statements vs. Multiple Updates

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Rex Gibson (RexGibson_at_discussions.microsoft.com)
Date: 11/04/04 

Date: Thu, 4 Nov 2004 07:28:02 -0800

Here Is what I mean.

update a
set response = case when isnull(b.met_status,'') = 'A21' then 21
                        when isnull(b.met_status,'') = 'A22' then 22
                        when isnull(b.met_status,'') = 'A23' then 23
                        when isnull(b.met_status,'') = 'A24' then 24
                        when isnull(b.met_status,'') = 'A25' then 25
                        when isnull(b.met_status,'') = 'A26' then 26
                        when isnull(b.met_status,'') = 'A27' then 27
                        when isnull(b.met_status,'') = 'A28' then 28
                        else 5 end
from #t1 a join met_callcenter_live..call_log b
        on a.customer_no =b.mmid# and a.source_no = b.source_no
where b.date_time between '10-13-2004' and '10-13-2004 23:59:59:999'
        and a.response = 5 -- Telephone No-Sale

OR DOING THIS 8 TIMES:

update a
set response = 21
>From #t1 a join met_callcenter_live..call_log b
        on a.customer_no =b.mmid# and a.source_no = b.source_no
where b.date_time between '10-13-2004' and '10-13-2004 23:59:59:999'
        and a.response = 5
        and isnull(b.met_status,'') = 'A21'

update a
set response = 22
>From #t1 a join met_callcenter_live..call_log b
        on a.customer_no =b.mmid# and a.source_no = b.source_no
where b.date_time between '10-13-2004' and '10-13-2004 23:59:59:999'
        and a.response = 5
        and isnull(b.met_status,'') = 'A22'

NOTES this is pulling data from another DB on the same server.

Make sense?

"Zach Wells" wrote:

> Rex Gibson wrote:
> > Have a theory question:
> >
> > Which is FASTER a single select statment with 8 WHEN clauses or 8
> > update statements.
> >
> > I assume the case statement but I am not sure.
> >
> > Are there circumstances when one might be faster than the other?
> >
> >
>
> Your question doesn't make a whole lot of sense. CASE is an expression
> that returns a single value. Update updates a table and logs the changes
> to a transaction log. CASE will obviously be faster.
>
> Zach
>