Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2011
    Posts
    220

    Unanswered: update or insert query from one table to another

    Hi;

    FOr Existing,we have joined the another table TABLE2 with TABLE1 to handle the MISC_TXT column

    so that to avoid the another table joins,added MISC_TXT column in Table1

    MISC_TXT columns get inserted by all the time with the latest TIME STAMP details
    in the TABLE2,so the TABLE2 has all the rows with latest TIME STAMP


    I want the qurey like,to select the latest TIME STAMP MISC_TXT from TABLE2 and has to INSERT or UPDATE

    in to the MISC_TXT column in TABLE1


    TABLE1 Composite Keys are

    P_LOC,P_NOR,IN_DT

    TABLE2 composite keys are

    P_LOC,P_NOR,TIME_UPD

    I am trying the below query and gets error
    Code:
    UPDATE TABLE1 T1
    
     SET T1.MISC_TXT = (SELECT 
                      T2.MISC_TXT
                      ,ROW_NUMBER() OVER PARTITION BY
                        (T2.P_LOC
                         T2.P_NOR
                    ORDER BY TIME_UPD DESC ) AS RN
              
                FROM TABLE2 T2
    WHERE 
    
        T1.P_LOC = T2.PLOC
    AND T1.P_NOR = T2.P_NOR
    )
    
    WHERE 
     RN=1
    TABLE1
    Code:
    p_loc   p_NOR    IN_DT                MISC_TXT
    A11     1234     2001-01-02 11    
    B11     1311     2006-10-02 11    
    C11     1411     2011-09-02 11
    TABLE2
    Code:
    p_loc   p_NOR        MISC_TXT    TIME_UPD
    A11     1234          TEST1       2003-11-12 21:50:24.842321
    A11     1234          TEST11      2006-11-02 11:50:24.742323
    A11     1234          TEST112     2011-01-11 22:51:21.752333
    B11     1311          TEST2       2007-12-22 11:50:26.942324  
    C11     1411          TEST3       2009-11-11 21:50:24.842321
    C11     1411          TEST3       2010-11-01 21:50:24.742321
    C11     1411          TEST333     2011-11-12 11:40:24.442325
    Expected result set of TABLE1 after INSERTs or UPDATEs
    Code:
    p_loc   p_NOR    IN_DT                MISC_TXT
    A11     1234     2001-01-02 11          TEST112
    B11     1311     2006-10-02 11          TEST2
    C11     1411     2011-09-02 11          TEST333
    Pl help

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    A solution may be to use a MERGE statement.

    Put your subquery into USING clause of the MERGE statement.

    But, I thought at least three modifications might be necessary in your subquery.
    (1) A left parenthesis of ROW_NUMBER OLAP specification was misplaced.

    (2) Add T2.P_LOC and T2.P_NOR into SELECT clause to use in later ON clause of MERGE statement.

    (3) Remove WHERE cluase to use your subquery in the USING clause.
    (Generally, this is not neccesary. But, it may be neccesary at this time.)

    An example considering the three issues might be:
    Code:
    ...
    /* SET T1.MISC_TXT = */ (SELECT T2.P_LOC , T2.P_NOR
                      ,ROW_NUMBER() OVER( PARTITION BY
                        /*(*/T2.P_LOC
    ...
    /*
    WHERE 
    
        T1.P_LOC = T2.PLOC
    AND T1.P_NOR = T2.P_NOR
    */
    )
    ...

    Please see the references I posted in another thread for MERGE statement.

    Quote Originally Posted by tonkuma View Post

  3. #3
    Join Date
    Sep 2011
    Posts
    220
    Hi;

    I was trying the below query and facing the error like "SQL0199N The use of the reserved word "SELECT" following "" is not valid. Expected tokens may include: "VALUES".

    Code:
    MERGE INTO TABLE1 AS T1
      USING (SELECT P_LOC, P_NOR
             FROM (SELECT P_LOC, P_NOR,MISC_TXT,
                   ROW_NUMBER() OVER (PARTITION BY  P_LOC, P_NOR
                   ORDER BY TIME_UPD DESC) rn
                   FROM TABLE2) AS NN
                   WHERE rn = 1) AS TT
      ON  TT.P_LOC     = T1.P_LOC    
      AND TT.P_NOR    = T1.P_NOR  
      
      WHEN MATCHED 
    
    THEN
         UPDATE SET
            MISC_TXT = TT.MISC_TXT
       
      WHEN NOT MATCHED THEN
         INSERT
            MISC_TXT
            VALUES (TT.MISC_TXT)

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please see syntax diagram and compare it with your code.

    Code:
    insert-operation
    
    |--INSERT--+-----------------------+---------------------------->
               |    .-,-----------.    |   
               |    V             |    |   
               '-(----column-name-+--)-'   
    
    >--VALUES--+-+-expression-+-----------+-------------------------|
               | +-DEFAULT----+           |   
               | '-NULL-------'           |   
               |    .-,--------------.    |   
               |    V                |    |   
               '-(----+-expression-+-+--)-'   
                      +-DEFAULT----+          
                      '-NULL-------'

    And, there is no "TT.MISC_TXT" in USING clause.
    Last edited by tonkuma; 12-16-11 at 09:37. Reason: Add second phrase "And, there is no ..."

  5. #5
    Join Date
    Sep 2011
    Posts
    220
    Code:
    MERGE INTO TABLE1 AS T1
      USING (SELECT P_LOC, P_NOR,MISC_TXT
             FROM (SELECT P_LOC, P_NOR,MISC_TXT,
                   ROW_NUMBER() OVER (PARTITION BY  P_LOC, P_NOR
                   ORDER BY TIME_UPD DESC) rn
                   FROM TABLE2) AS NN
                   WHERE rn = 1) AS TT
      ON  TT.P_LOC     = T1.P_LOC    
      AND TT.P_NOR    = T1.P_NOR  
      
      WHEN MATCHED 
    
    THEN
         UPDATE SET
            (MISC_TXT) = (TT.MISC_TXT)
       
      WHEN NOT MATCHED THEN
         INSERT
            (MISC_TXT)
            VALUES (TT.MISC_TXT)
    same error coming...Pl help

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please read through the error message carefully
    and see the executed SQL statement and compare it with the error message.

    It must be good practice for you.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The descriptions in Message Reference may help you.

    IBM DB2 9.7 for Linux, UNIX, and Windows Message Reference Volume 2
    SQL0199N
    ...
    User response:
    Examine the statement in the keyword area.
    Add a colon or SQL delimiter, if missing. Verify that the clauses are in the correct order.
    If the reserved word identified in the messages is listed as a reserved word, make the word a delimited identifier.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by Billa007 View Post
    Code:
    MERGE INTO TABLE1 AS T1
      USING (SELECT P_LOC, P_NOR,MISC_TXT
             FROM (SELECT P_LOC, P_NOR,MISC_TXT,
                   ROW_NUMBER() OVER (PARTITION BY  P_LOC, P_NOR
                   ORDER BY TIME_UPD DESC) rn
                   FROM TABLE2) AS NN
                   WHERE rn = 1) AS TT
      ON  TT.P_LOC     = T1.P_LOC    
      AND TT.P_NOR    = T1.P_NOR  
      
      WHEN MATCHED 
    
    THEN
         UPDATE SET
            (MISC_TXT) = (TT.MISC_TXT)
       
      WHEN NOT MATCHED THEN
         INSERT
            (MISC_TXT)
            VALUES (TT.MISC_TXT)
    same error coming...Pl help
    Could you solved your issue?

    Your MERGE statement was executed successfully in my test.
    Note 1: The executed statement was copied from your post and added qualifier "Billa007." to TABLE1 and TABLE2.
    Note 2: A semicolon at the last of the statement was added automatically by Command Editor.
    Code:
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO Billa007.table1
    VALUES
      ( 'A11' , '1234' , '2001-01-02' , '' )
    , ( 'B11' , '1311' , '2006-10-02' , '' )
    , ( 'C11' , '1411' , '2011-09-02' , '' );
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO Billa007.table2
    VALUES
      ( 'A11' , '1234' , 'TEST1'   , '2003-11-12 21:50:24.842321' )
    , ( 'A11' , '1234' , 'TEST11'  , '2006-11-02 11:50:24.742323' )
    , ( 'A11' , '1234' , 'TEST112' , '2011-01-11 22:51:21.752333' )
    , ( 'B11' , '1311' , 'TEST2'   , '2007-12-22 11:50:26.942324' )
    , ( 'C11' , '1411' , 'TEST3'   , '2009-11-11 21:50:24.842321' )
    , ( 'C11' , '1411' , 'TEST3'   , '2010-11-01 21:50:24.742321' )
    , ( 'C11' , '1411' , 'TEST333' , '2011-11-12 11:40:24.442325' );
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    MERGE INTO Billa007.TABLE1 AS T1
      USING (SELECT P_LOC, P_NOR,MISC_TXT
             FROM (SELECT P_LOC, P_NOR,MISC_TXT,
                   ROW_NUMBER() OVER (PARTITION BY  P_LOC, P_NOR
                   ORDER BY TIME_UPD DESC) rn
                   FROM Billa007.TABLE2) AS NN
                   WHERE rn = 1) AS TT
      ON  TT.P_LOC     = T1.P_LOC    
      AND TT.P_NOR    = T1.P_NOR  
      
      WHEN MATCHED 
    
    THEN
         UPDATE SET
            (MISC_TXT) = (TT.MISC_TXT)
       
      WHEN NOT MATCHED THEN
         INSERT
            (MISC_TXT)
            VALUES (TT.MISC_TXT);
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM Billa007.TABLE1;
    ------------------------------------------------------------------------------
    
    P_LOC P_NOR   IN_DT      MISC_TXT  
    ----- ------- ---------- ----------
    A11   1234    2001-01-02 TEST112   
    B11   1311    2006-10-02 TEST2     
    C11   1411    2011-09-02 TEST333   
    
      3 record(s) selected.

  9. #9
    Join Date
    Sep 2011
    Posts
    220
    Hi;

    I was executed the above MERGE query,but getting the same error,So that i was using the below query and updated successfully

    Code:
    UPDATE TABLE1 E
    SET E.MISC_TXT = ( SELECT T.MISC_TXT FROM TABLE2 T 
    WHERE 
    
      T.P_LOC     = E.P_LOC     
      AND T.P_NOR    = E.P_NOR    
    
    ORDER BY T.TIME_UPD DESC
     
    FETCH FIRST 1 ROWs ONLY 
    )
    WHERE EXISTS (SELECT 1 FROM TABLE2 T
    
     WHERE 
    
      T.P_LOC     = E.P_LOC     
      AND T.P_NOR    = E.P_NOR    
      
    )

Posting Permissions

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