[Info-ingres] MERGE syntax

[Roy Hann]

I've recently run into a problem that cannot be solved except using a
MERGE statement. I should probably have taken an interest in it long ago
but...let's just say none of my customers like to be pioneers. Don't
dwell on that; just be pleased I'm finally using it.

The EBNF specification of the syntax in the SQL Guide is not quite right
(it doesn't indicate the keyword "THEN" has to precede the matching
action specification). But it does show that multiple matching actions
(UPDATE or DELETE) can be specified:

   WHEN MATCHED [AND condition]
       {UPDATE SET col = expr,... | DELETE}

I have not been able to quickly contrive an example with multiple UPDATE
actions that will even parse. For instance, ignoring the lack of any
mention of THEN, the above seems to allow:

   MERGE INTO master_table t USING trx x               
       ON t.acct_no = x.acct_no                        
   WHEN MATCHED AND x.acct_no = 2 OR x.acct_no = 99    
       THEN UPDATE SET balance = t.balance + x.balance 
            UPDATE SET balance = t.balance + -1.0      
   WHEN NOT MATCHED                                    
       THEN INSERT VALUES (x.acct_no, x.balance+10.)

but it elicits:

   E_US09E6 line 1, Syntax error on 'UPDATE'.  The correct syntax is:      
    MERGE INTO target-table [AS corr] USING table-ref ON join-condition 
         WHEN MATCHED [AND condition]                                   
             {UPDATE SET col = expr,...  |  DELETE}                     
         WHEN NOT MATCHED [AND condition]                               
             INSERT [(col-list)] VALUES (expr-list)  

I've tried variations, like putting THEN before the second UPDATE as
well, but none work.

I am just trying to understand the syntax. I can't think of a reason to 
ever do multiple updates with the same matching condition.

Roy