Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2016
    Posts
    9
    Provided Answers: 2

    Question Answered: Update Global Temporary Table (Sample code provided)

    I have been working in T-SQL for many years and need to transition to DB2. I'm finding the syntax is different in many areas so I'm trying to adjust and become better acclimated with this. I can not get my code to Update a temporary table. I am supplying a sample code just for sake of an example. Can someone please advise where I am going wrong with this? I am only using 2 tables just to make it simple as to what I am trying to accomplish as my real code has many other ways I need to update data.

    I keep getting an error at the FROM part.

    Code:
    DECLARE GLOBAL TEMPORARY TABLE SESSION.TEST_TABLE
    (
    TMP_TIME	TIMESTAMP(6)	,
    TMP_ORDER_ID	INTEGER,
    TMP_PRIORITY_CODE	CHARACTER(8
    ) 
    ON COMMIT PRESERVE ROWS not logged with replace;
    
    insert into SESSION.TEST_TABLE 
    select distinct
    ORDER_TIME,
    ORDER_ID,
    NULL
    from
    TABLE 1
    where 
    ORDER_TIME between '8/1/2016' and '8/3/2016'
    
    UPDATE SESSION.TEST_TABLE
    SET
    TMP_PRIORITY_CODE = priority_code
    FROM
    TABLE 2
    WHERE
    TMP_ORDER_ID = PRIORITY_ORDER_ID;
    
    select * from SESSION.TEST_TABLE;
    
    drop table SESSION.TEST_TABLE;

  2. Best Answer
    Posted by db2mor

    "UPDATE session.test_table T set T.tmp_priority_code = (select S.priority_code from table2 S where S.priority_order_id = T.tmp_order_id)

    MERGE is an SQL statement (also in t-sql) which can combine inserts/updates/delete in single statement, and you can also merge into a session table.
    Depending on cardinalities, you may need to index+runstats the session table appropriately , and ensure the other table is suitably indexed, if you need to scale."


  3. #2
    Join Date
    Apr 2012
    Posts
    1,143
    Provided Answers: 27
    Post the exact error code, the line that's failing, the DB2-version+fixpack and platform(Windows/Aix/Os400/Z-os).
    Your sample syntax is invalid for the Update statement , and that's nothing to do with the DGTT as far as I can see - you might use a correlated subselect there . Also consider if you can achieve the overall aim with a suitable merge statement instead of insert+update.

  4. #3
    Join Date
    Oct 2016
    Posts
    9
    Provided Answers: 2
    10:51:12.617 DBMS EDT6P -- [IBM][CLI Driver][DB2/AIX64] SQL0104N An unexpected token "FROM" was found following "CODE = priority_code". Expected tokens may include: "CONCAT". SQLSTATE=42601

    It falls on the UPDATE line and I'm using DB2 10.5 via my Embarcadero Rapid SQL tool.

  5. #4
    Join Date
    Apr 2012
    Posts
    1,143
    Provided Answers: 27
    The message indicates your syntax is invalid. You did not post the failing statement. Have you fixed your broken UPDATE syntax or used MERGE?

  6. #5
    Join Date
    Oct 2016
    Posts
    9
    Provided Answers: 2
    I am giving you the error message:


    [IBM][CLI Driver][DB2/AIX64] SQL0104N An unexpected token "FROM" was found following "CODE = priority_code". Expected tokens may include: "CONCAT". SQLSTATE=42601
    (42601,-104).

    I'm trying to understand what is wrong with my syntax. Why doesn't it like the "From" based on the example I provided? Not sure what you mean by Merge?

  7. #6
    Join Date
    Apr 2012
    Posts
    1,143
    Provided Answers: 27
    UPDATE session.test_table T set T.tmp_priority_code = (select S.priority_code from table2 S where S.priority_order_id = T.tmp_order_id)

    MERGE is an SQL statement (also in t-sql) which can combine inserts/updates/delete in single statement, and you can also merge into a session table.
    Depending on cardinalities, you may need to index+runstats the session table appropriately , and ensure the other table is suitably indexed, if you need to scale.

  8. #7
    Join Date
    Oct 2016
    Posts
    9
    Provided Answers: 2
    I can't believe I missed that. I took that approach but did not assign an alias to the temp table in the sub select statement. That worked. Thank you so much.

Posting Permissions

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