1. Forum
  2. Usenet
  3. COMP.DATABASES.INGRES

  • MERGE syntax

    From Roy Hann@21:1/5 to All on Thu Oct 26 13:15:50 2023
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Roy Hann@21:1/5 to Roy Hann on Thu Oct 26 16:37:45 2023
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)