Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2013
    Posts
    2

    Unanswered: DB2 MERGE statement error

    I have tried the following with a couple of variations but I continue to get errors. Any way to get this fixed. DB2 10.1 (DB2 for z/OS V10)

    --------------------------------------------
    For the following

    MERGE INTO TRGT t
    USING SRC s
    ON (t.ACCTID=s.ACCTID AND s.SEQID=123)
    WHEN MATCHED THEN
    UPDATE SET
    MyFlag = 'Y'

    Error: An unexpected token "SRC" was found following "". Expected tokens may include: "(". SQLSTATE=42601

    SQLState: 42601 ErrorCode: -104

    --------------------------------------------
    However for the following

    MERGE INTO TRGT t
    USING (SELECT SEQID, ACCTID FROM SRC WHERE SEQID=123) s
    ON (t.ACCTID=s.ACCTID)
    WHEN MATCHED THEN
    UPDATE SET
    MyFlag = 'Y'

    Error: The use of the reserved word "SELECT" following "" is not valid. Expected tokens may include: "VALUES". SQLSTATE=42601

    SQLState: 42601 ErrorCode: -199

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please see the syntax diagram of MERGE statement on DB2 10.1 for z/OS.

    DB2 10 - DB2 SQL - MERGE

    Syntax

    Code:
    >>-MERGE INTO--+-table-name-+--+--------------------------+----->
                   '-view-name--'  | .-AS-.                   |   
                                   '-+----+--correlation-name-'   
    
    >--+---------------------+--USING--| source-table |------------->
       '-| include-columns |-'                            
    
    >--ON--search-condition----------------------------------------->
    
    ...
    ...
    source-table:

    Code:
    >>-(VALUES-+-| values-single-row |-------+-)-------------------->
               |                         (1) |     
               '-| values-multiple-row |-----'     
    
                                   .-,-----------.     
       .-AS-.                      V             |     
    >--+----+--correlation-name--(---column-name-+-)---------------><
    The error messages pointed out the syntax violation for source-table:.
    Error: An unexpected token "SRC" was found following "". Expected tokens may include: "(".
    Error: The use of the reserved word "SELECT" following "" is not valid. Expected tokens may include: "VALUES".

    Anyway,
    you may want to try
    Code:
    UPDATE TRGT t
     SET   MyFlag = 'Y'
     WHERE EXISTS
           (SELECT *
             FROM  SRC s
             WHERE s.ACCTID = t.ACCTID
               AND s.SEQID  = 123
           )

  3. #3
    Join Date
    Nov 2013
    Posts
    2

    DB2 MERGE statement error

    Thanks Tonkuma.

    I also have an insert that I wanted to do, hence I was trying to use MERGE. Looks like MERGE will in my environment will not support what I want to do.

    I have to use separate UPDATE and INSERT statements to accomplish my job.

    Thanks again.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If the statement was built into C/C++, COBOL, or PL/I,
    host-variable-array might be an option.

    You can see the following syntax diagram after source-table: in
    DB2 10 - DB2 SQL - MERGE

    values-multiple-row:

    Code:
    >>-+-+-expression----------+---------+-------------------------->
       | '-host-variable-array-'         |   
       |   .-,-----------------------.   |   
       |   V                         |   |   
       '-(---+-expression----------+-+-)-'   
             '-host-variable-array-'         
    
                                       (1)   
    >--FOR--+-host-variable----+--ROWS-----------------------------><
            '-integer-constant-'

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •