Using Case Statement with Merge 1

[HoustonGuy]

I can't get the syntax correct for using a case statement within a Merge Statement.
I've tried many variations. I found an example online once, but can't find it now. All of the online samples don't show conditional statements with the Merge Statement.

Here's what I'm trying to do:

MERGE dbo.Target AS T -- Target
USING dbo.Source AS S -- Source
ON T.ID = S.ID

WHEN MATCHED THEN 
  UPDATE SET 
	Case S.CountField
	WHEN 1 THEN 
		 T.Field1 = S.Field1
		,T.Field2 = S.Field2
                ,T.Field3 = S.Field3
	WHEN 2 THEN 
		 T.Field1 = S.Field11
		,T.Field2 = S.Field12
                ,T.Field3 = S.Field13
	end
	;

Thank you in advance!

 

[bborissov]

MERGE dbo.Target AS T -- Target
USING dbo.Source AS S -- Source
ON T.ID = S.ID

WHEN MATCHED THEN 
  UPDATE SET 
		 T.Field1 = CASE WHEN S.CountField = 1 THEN S.Field1 ELSE S.Field11 END
		,T.Field2 = CASE WHEN S.CountField = 1 THEN S.Field2 ELSE S.Field12 END
                ,T.Field3 = CASE WHEN S.CountField = 1 THEN S.Field3 ELSE S.Field13 END
	;

Borislav Borissov
VFP9 SP2, SQL Server

 

[HoustonGuy]

Thanks bborissov!

Will the case statements still be faster than separate updates statements? (Not using Merge)
I'll still benefit from the "one pass" capabilities of the Merge statement?

 

[HoustonGuy]

My original example showed two scenarios, but I have more. So I can't use a simple ELSE option.
(I'm sorry for my poor example)

I need to have multiple updates. Such as:

MERGE dbo.Target AS T -- Target
USING dbo.Source AS S -- Source
ON T.ID = S.ID

WHEN MATCHED THEN
UPDATE SET
T.Field1 = CASE WHEN S.CountField = 1 THEN S.Field1 END
,T.Field2 = CASE WHEN S.CountField = 1 THEN S.Field2 END
,T.Field3 = CASE WHEN S.CountField = 1 THEN S.Field3 END

,T.Field4 = CASE WHEN S.CountField = 2 THEN S.Field11 END
,T.Field5 = CASE WHEN S.CountField = 2 THEN S.Field12 END
,T.Field6 = CASE WHEN S.CountField = 2 THEN S.Field13 END

,T.Field7 = CASE WHEN S.CountField = 3 THEN S.Field14 END
,T.Field8 = CASE WHEN S.CountField = 3 THEN S.Field15 END
,T.Field9 = CASE WHEN S.CountField = 3 THEN S.Field16 END

ETC...
;

Is this possible?
THANK YOU for your help - and everyone else's.

 

[bborissov]

MERGE dbo.Target AS T -- Target
USING dbo.Source AS S -- Source
ON T.ID = S.ID

WHEN MATCHED THEN
UPDATE SET
 T.Field1 = CASE WHEN S.CountField = 1 THEN S.Field1 ELSE T.Field1 END --- Just update the field by itself in ELSE
,T.Field2 = CASE WHEN S.CountField = 1 THEN S.Field2 ELSE T.Field2 END
,T.Field3 = CASE WHEN S.CountField = 1 THEN S.Field3 ELSE T.Field2 END

,T.Field4 = CASE WHEN S.CountField = 2 THEN S.Field11 ELSE T.Field4 END
ETC...

Borislav Borissov
VFP9 SP2, SQL Server

 

[HoustonGuy]

I tried this and get the following error:
The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.

There is a one to many relationship between the Source Table and the Target Table.
I think this is the reason for the error above.

Source Table ID Target Table ID
1 1
1
1
2 2
2
etc...

 

[HoustonGuy]

my example didn't show up correctly above, sorry.

The code you gave is syntactically correct. But the one to many relationship causes the error.

Source Table ID       Target Table ID
1                      1
                       1
                       1
2                      2
                       2
etc..

 

[gk53]

it is required join by unique values...
use update like

update T
SET
 T.Field1 = CASE WHEN S.CountField = 1 THEN S.Field1 ELSE T.Field1 END --- Just update the field by itself in ELSE
,T.Field2 = CASE WHEN S.CountField = 1 THEN S.Field2 ELSE T.Field2 END
,T.Field3 = CASE WHEN S.CountField = 1 THEN S.Field3 ELSE T.Field2 END
,T.Field4 = CASE WHEN S.CountField = 2 THEN S.Field11 ELSE T.Field4 END
from dbo.Target AS T
join dbo.Source AS S -- Source
ON T.ID = S.ID

 

[HoustonGuy]

I'm already using an Update statement to do the job now.
I'm trying to see if the Merge Statement can be used in my case.

 

[gk53]

I do not see any advantage of merge over update in this case if you have only WHEN MATCHED THEN condition...

 

[HoustonGuy]

Merge cannot handle a one to many relationship between the Source table and the Target table.
I was hoping it would, somehow. But it makes sense, since Merge is a single pass.

Thanks to everyone for the help.