update or insert query from one table to another

Billa007 · #1 (**permalink**) 12-16-11, 03:28

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

tonkuma · #2 (**permalink**) 12-16-11, 04:51

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

...

See
page 24-27 in SQL on Fire! Part 2
http://sirdug.org/downloads/SQLonFire_2_SirDUG.pdf
or
Information Center

MERGE - IBM DB2 9.7 for Linux, UNIX, and Windows

Billa007 · #3 (**permalink**) 12-16-11, 07:38

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)

tonkuma · #4 (**permalink**) 12-16-11, 08:17

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.

Billa007 · #5 (**permalink**) 12-16-11, 08:42

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

tonkuma · #6 (**permalink**) 12-16-11, 09:26

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.

tonkuma · #7 (**permalink**) 12-16-11, 09:37

The descriptions in Message Reference may help you.

IBM DB2 9.7 for Linux, UNIX, and Windows Message Reference Volume 2

Quote:

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.

tonkuma · #8 (**permalink**) 12-16-11, 16:25

Quote:

Originally Posted by Billa007

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.

Billa007 · #9 (**permalink**) 12-20-11, 03:18

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    
  
)

Internet Services

Web Development

Digital Marketing

Consumer Tech