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

[Roy Hann]

Roy Hann wrote:

> 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.

Unless someone tells me different, I'm starting to think this is just
another example where EBNF runs out of steam.

I think that description is just trying to tell us it doesn't matter
in what order we specify the DELETE action and the UPDATE action.
Semantically, we're allowed only one of each.

Am I right?

Roy