I need your help pls...I have a requirement in merge -

how do I handle below scenario? I know i can't put 3 matched. 

when matched and s.efforts =0 then update table1 set field1=somevalue, field2=somevalue

when matched and s.efforts <>0 and target.field1<>0 then update table1 set field1=source.somevalue, field2=target.somevalue

when matched and s.efforts <>0 and target.field1=0 then update table1 set field1=source.somevalue, field2=target.somevalue

UPDATE
SET    field1 = CASE WHEN s.efforts = 0 THEN s.somevalue
                     WHEN target.field1 <> 0 THEN s.someothervalue
                     ELSE target.field1
                END

And on it goes. That is, you need encode the different alternatives column per column.

I agree that it would have been neater if we could have had multiple WHEN MATCHED clauses with UPDATE. I ran into this myself the other day.

How do I add 2nd field2 in above update statement

You put a comma after END, and the you type field2 followed by en equal sign which then is followed by the desired expression, just like you had in your initial post.

Thanks for your help. I have one more question.

I have source  & destination tables. I have one more table table3. I want to insert data into table 3 into a field from source.id. How do I do this?

The normal approach would be to have a separate INSERT statement, although it is possible to do in a single statement with composable DML. That is, then you do:

INSERT (....)
   SELECT ...
   FROM   (MERGE ...
           OUTPUT ...) AS mer
   WHERE  ...

The columns in the OUTPUT clause in the MERGE statement that you can use in the surrounding SELECT statement.

I'm not so fond over that syntax, because it's kind of too much at the same time, but there are situations where it comes in handy, and you are saved from having an intermediate table.

I have to insert values into table - "journal" and my merge statement goes like this...

MERGE table1 AS target
USING (
    SELECT a.aid,a.efforts,ab.lcs
    FROM cip a
    INNER JOIN dc ab ON a id = ab.id
    WHERE a.id = @id
    ) AS source
    ON ( target.caseid = @caseidupd
            AND target.aid = source.aid
            )
WHEN MATCHED AND source.lc IN (1,2)
    THEN
        UPDATE
        SET target curr = CASE
                WHEN source.efforts = 0
                    THEN source.efforts source.efforts <> 0 then target.curr
                END
            ,target.modi = CASE
                WHEN source.efforts = 0
                    THEN getdate() source.efforts <> 0 then getdate()
                END
WHEN NOT MATCHED BY target
    THEN
        INSERT (caseid,aid,modi)
        VALUES (@caseidupd,source.aid,getdate())

--this is additional table "journal", which I want to insert source.aid same value in the below table also in the above merge.
INSERT journal (textname,caseid,aid,modi)
VALUES ('new record created',@caseidupd,source.aid,getdate())

You could try:

INSERT journal (textname,caseid,aid,modi)
   SELECT 'new recored created', @casidupd, aid, gedate()
   FROM   (MERGE table1 AS target
           ...
           OUTPUT source.aid) AS m

Is it possible can I put in a better way of below statements -In "journal" table, I have to apply select statement wt case.(i think case syntax needs to modify).

MERGE table1 AS target
USING (
    SELECT a.aid,a.efforts,ab.lcs
    FROM cip a INNER JOIN dc ab ON a id = ab.id
    WHERE a.id = @id
    ) AS source
    ON ( target.caseid = @caseidupd
            AND target.aid = source.aid
            )
WHEN MATCHED AND source.lc IN (1,2)
    THEN
        UPDATE
        SET target curr = CASE
                WHEN source.efforts = 0
                    THEN source.efforts source.efforts <> 0 then target.curr
                END
            ,target.modi = CASE
                WHEN source.efforts = 0
                    THEN getdate() source.efforts <> 0 then getdate()
                END
WHEN NOT MATCHED BY target
    THEN
        INSERT (caseid,aid,modi)
        VALUES (@caseidupd,source.aid,getdate()


MERGE journal AS target
USING (
    SELECT a.aid,a.efforts,ab.lcs
    FROM cip a INNER JOIN dc ab ON a id = ab.id
    WHERE a.id = @id
    ) AS source
    ON ( target.caseid = @caseidupd
            AND target.aid = source.aid
            )
WHEN MATCHED AND source.lc IN (1,2)
    THEN
     
INSERT (caseid,aid,modi)
 select @caseidupd,'appupdated'+source.aid,getdate()
     target curr = CASE
                WHEN source.efforts = 0
                    THEN source.efforts source.efforts <> 0 then target.curr
                END
            ,target.modi = CASE
                WHEN source.efforts = 0
                    THEN getdate() source.efforts <> 0 then getdate()
                END
WHEN NOT MATCHED BY target
    THEN
        INSERT (caseid,aid,modi)
        VALUES (@caseidupd,'appinserted'+source.aid,getdate()

• Edited by kdinuk Wednesday, April 30, 2014 1:10 PM

Thanks Erland. I have a doubt. I want to have some thing like this....Can I put like this? I'm getting an issue.

INSERT journal (textname,caseid,aid,modi)
   SELECT case when m.action='insert' then 'new recored created' +aid

when m.action='update' then 'record updated'+ aid, @casidupd, aid, gedate()
   FROM   (MERGE table1 AS target
           ...
           OUTPUT source.aid) AS m

or after when not matched by target

OUTPUT

case when action='insert' then 'new record created' +aid

when action='update' then 'record updated'+ aid end as text, @casidupd, aid, gedate()

• Edited by kdinuk Wednesday, April 30, 2014 11:01 PM

Thanks Erland. I have a doubt. I want to have some thing like this....Can I put like this? I'm getting an issue.

And the issue is?

Thanks once again. I have a doubt. I 'curr' value change to 'tar' then how do I display in output clause. I have added additonal sql which is in bold

MERGE table1 AS target
USING (
    SELECT a.aid,a.efforts,ab.lcs
    FROM cip a
    INNER JOIN dc ab ON a id = ab.id
    WHERE a.id = @id
    ) AS source
    ON ( target.caseid = @caseidupd
            AND target.aid = source.aid
            )
WHEN MATCHED AND source.lc IN (1,2)
    THEN
        UPDATE
        SET target curr = CASE
                WHEN source.efforts = 0
                    THEN source.efforts source.efforts <> 0 then source.efforts
                END
    SET target tar= CASE
                WHEN source.efforts = 0
                    THEN source.efforts source.efforts <> 0 then target.curr
                END
            ,target.modi = CASE
                WHEN source.efforts = 0
                    THEN getdate() source.efforts <> 0 then getdate()
                END
WHEN NOT MATCHED BY target
    THEN
        INSERT (caseid,aid,modi)
        VALUES (@caseidupd,source.aid,getdate())
output $action
case when $action='UPDATE' THEN 'APPUPDATE'
when $action='INSERT' THEN 'APPINSERT'
--here I want to add one more condition. If source.efforts=0 then it should be
case when $action='UPDATE' THEN 'APPzeroed'
--here I want to add one more condition. If source.efforts=0 then it should be
case when $action='UPDATE' THEN 'source value' +curr +'changed to'+tar

*--here I want to add one more condition. If source.efforts=0 then it should be* *case when $action='UPDATE' THEN 'APPzeroed'*

Doesn't this work?

case when $action='UPDATE' AND source.efforts = 0 THEN 'APPzeroed'

and the same for the other one.

I think you're trying to show values that changed in the update, the trick is compare inserted.[col] to the deleted.[col] -- deleted.[col] actually holds the source table value (for the update)

this is how I did it:

OUTPUT $action as [action],
coalesce (inserted.arcustkey, deleted.arcustkey) as prikey
,CASE WHEN inserted.[CustName] <> deleted.[CustName] THEN s.[CustName] END as [CustName]

it shows you the col & value that changed

could do things like this also:

,CASE WHEN inserted.[CustName] <> deleted.[CustName] THEN deleted.[CustName] + ' --> ' + inserted.[CustName] END as [CustName]

UPDATE 101036 NULL newname --> newname_again

• Edited by Scott C Kramer 3 hours 29 minutes ago