Syntax for multiple updates in a case statement 2

[HoustonGuy]

I have multiple fields to update based on a single field's condition.
I can't find the proper format.

Update Table1
Set Field1 = CASE when Field85 <> '' then isnull(A.Field2, ''),
Field2 = isnull(A.Field3, ''),
Field3 = isnull(A.Field4, '')
else
Field1 = isnull(A.Field1, ''),
Field2 = isnull(A.Field2, ''),
Field3 = isnull(A.Field3, '')end
from #TempTable A
Join table1 B
on A.IDnumber = B.IDnumber

Based on the contents of field I need to determine the updates on a lot of fields.
Is it possible to use a case to do multiple updates in a single statement?

I hope my example is clear enough - if not I'll clarify.
Thank you to my brothers and sisters in advance!

 

[gmmastros]

Unfortunately, you'll need to repeat the condition for each column, like this:

Update Table1
Set    Field1 = CASE when Field85 <> '' then isnull(A.Field2, '') else isnull(A.Field1, '') End,
       Field2 = CASE when Field85 <> '' then isnull(A.Field3, '') else isnull(A.Field2, '') end,
       Field3 = CASE when Field85 <> '' then isnull(A.Field4, '') else isnull(A.Field3, '') End
from #TempTable A
Join table1 B
on A.IDnumber = B.IDnumber

It's not pretty, but it works.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[HoustonGuy]

Thanks George!

Eeeek - that's what I was afraid of.
I have many fields to update - more than shown in my example.

I'm wondering if it would be beneficial to create a temp table with values from each set - then just update against the temp tables based on the condition.

Something like:

If field85 <> '' then
begin
update table1
Set Field1 = isnull(A.Field2, ''),
Field2 = isnull(A.Field3, ''),
Field3 = isnull(A.Field4, '')
from #TempTable A
end

If field85 = '' then
begin
update table1
Set Field1 = isnull(A.Field1, ''),
Field2 = isnull(A.Field2, ''),
Field3 = isnull(A.Field3, '')
from #TempTable A
end

 

[fredericofonseca]

look at the merge statement

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[gmmastros]

As ugly as the code looks, it probably performs the best (only testing would tell for sure).

Doing multiple updates isn't a bad idea though. If field85 is a column in the table, then you probably want this...

update table1
Set    Field1 = isnull(A.Field2, ''),
       Field2 = isnull(A.Field3, ''),
       Field3 = isnull(A.Field4, '')
from   #TempTable A
       Inner Join table1
         on A.PrimaryKeyColumn = table1.PrimaryKeyColumn
Where  A.Field85 <> ''

update table1
Set    Field1 = isnull(A.Field1, ''),
       Field2 = isnull(A.Field2, ''),
       Field3 = isnull(A.Field3, '')
from   #TempTable A
       Inner Join table1
         on A.PrimaryKeyColumn = table1.PrimaryKeyColumn
Where  A.Field85 <> ''

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[HoustonGuy]

Thanks to you both.

George - the second set of code works great, and looks a lot cleaner.

Frederico - I've checked out the Merge statement, and it will work as well. I'm going to try this. I need to stay up on all the latest statements, as I have a bad habit of using the same few over and over.