INSERT with Delete query

Billa007 · #1 (**permalink**) 11-10-11, 05:20

Hi

We have requirement like whatever row deleted in the below query should insert to the
another table(REPORT_TABLE)

Code:

delete from MAIN_TABLE T1 
where T1.policy_LOC ='AA' 
and RECV_DT < (select MAX(T2.RECV_DT) 
      from MAIN_TABLE T2
where 
    T2.policy_LOC=T1.policy_LOC
AND T2.POLICY_NO =T1.POLICY_NO
AND T2.RECV_DT < CURRENT_DATE 

GROUP BY

T2.policy_LOC
,T2.POLICY_NO
  )

MAIN_TABLE column details

POLICY_LOC
,POLICY_NO
,HOLDER_NAME
,HOLDER_AGE
,MISC-TXT
,LOGIN_NAME
,PROGRAM_ID
,RECV_DT
,LAST_TIMESTAMP

REPORT_TABLE column details

POLICY_LOC
,POLICY_NO
,HOLDER_NAME
,HOLDER_AGE
,PROGRAM_ID
,RECV_DT
,LAST_TIMESTAMP
,PROCESS_TYPE

In the REPORT_TABLE should have whatever row is deleted from the MAIN_TABLE
with PROCESS_TYPE column as 'DEL' literal value and LAST_TIMESTAMP should have current Time stamp value
and we have to pass the Program name(RHRESAER) literaly to PROGRAM_ID column

MAIN_TABLE

Code:

POLICY_LOC   POLICY_NO HOLDER_NAME  HOLDER_AGE  MISC_TXT LOGIN_NAME PROGRAM_ID   RECV_DT        LAST_TIMESTAMP

AA            1234      STEVE        34           PREMI    DERE       FXTYUYER    2011-10-19   2011-10-11 14:46:54.511682
AA            1234      STEVE        34           PREMI    DERE       FXTYUYER    2011-11-10   2011-10-13 12:46:54.411683
AA            2345      ROBIN        44           PREMI    DERE       FXTYUYER    2011-10-30   2011-10-15 13:47:44.614681 
AA            2345      ROBIN        44           PREMI    DERE       FXTYUYER    2011-11-10   2011-10-21 13:47:45.314682
BB            9999      JAMES        24           PREMI    DERE       FXTYUYER    2011-12-21   2011-09-19 12:46:54.411683

REPORT_TABLE should have

Code:

POLICY_LOC   POLICY_NO HOLDER_NAME  HOLDER_AGE   PROGRAM_ID   RECV_DT        LAST_TIMESTAMP             PROCESS_TYPE

AA            1234      STEVE        34           RHRESAER    2011-10-19   2011-11-10 14:46:54.511682    DEL
AA            2345      ROBIN        44           RHRESAER    2011-10-30   2011-11-10 13:47:44.614681    DEL

Please help me on that..how to write INSERT query ?

tonkuma · #2 (**permalink**) 11-10-11, 06:14

Please try like this...
(not tested)

Code:

INSERT INTO report_table
SELECT ...
 FROM  OLD TABLE
       (delete from MAIN_TABLE T1
         where T1.policy_LOC ='AA'
           and RECV_DT
               < (select MAX(T2.RECV_DT) 
                    from MAIN_TABLE T2
                   where
                         T2.policy_LOC = T1.policy_LOC
                     AND T2.POLICY_NO  = T1.POLICY_NO
                     AND T2.RECV_DT    < CURRENT_DATE
                   GROUP BY
                         T2.policy_LOC
                       , T2.POLICY_NO
                 )
       ) d
;

Billa007 · #3 (**permalink**) 11-10-11, 06:44

Thanks for the quick reply

Quote:

INSERT INTO report_table
SELECT ...
FROM OLD TABLE

Please clarify..What do you mean byOLD TABLE ?
means like below

Code:

INSERT INTO report_table
SELECT *
 FROM  MAIN_TABLE

tonkuma · #4 (**permalink**) 11-10-11, 07:14

Please read the description of OLD TABLE in Data change table references in Information Center
subselect - IBM DB2 9.7 for Linux, UNIX, and Windows

Quote:

OLD TABLE

Specifies that the rows of the intermediate result table represent the set of rows that are changed by the SQL data change statement as they existed prior to the application of the data change statement.

dr_te_z · #5 (**permalink**) 11-10-11, 07:14

Quote:

Originally Posted by Billa007

Please clarify..What do you mean byOLD TABLE ?

Look at the last few slides from this guy's presentation: http://www.gse-nordic.org/Working%20...2%20Stream/s44
Once you found the answer to your question take some time to look at the whole presentation. Very informative.

tonkuma · #6 (**permalink**) 11-10-11, 07:22

I tried my sample INSERT statement and got an error.

Quote:

DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL20165N An SQL data change statement within a FROM clause is not allowed
in the context in which it was specified. SQLSTATE=428FL

Then, after some trial the following statement was successful.
Note1: I removed some columns from both tables to simplify my test.
Note2: I changed "AND T2.RECV_DT < CURRENT_DATE" to "AND T2.RECV_DT <= CURRENT_DATE" to delete/insert two rows.

Example 1:

Code:

------------------------------ Commands Entered ------------------------------
SELECT * FROM Billa007.main_table
;
------------------------------------------------------------------------------

POLICY_LOC   POLICY_NO PROGRAM_ID RECV_DT    LAST_TIMESTAMP            
------------ --------- ---------- ---------- --------------------------
AA                1234 FXTYUYER   2011-10-19 2011-10-11-14.46.54.511682
AA                1234 FXTYUYER   2011-11-10 2011-10-13-12.46.54.411683
AA                2345 FXTYUYER   2011-10-30 2011-10-15-13.47.44.614681
AA                2345 FXTYUYER   2011-11-10 2011-10-21-13.47.45.314682
BB                9999 FXTYUYER   2011-12-21 2011-09-19-12.46.54.411683

  5 record(s) selected.


------------------------------ Commands Entered ------------------------------
WITH
 delete_main AS (
SELECT *
 FROM  OLD TABLE
       (delete from Billa007.MAIN_TABLE T1
         where T1.policy_LOC ='AA'
           and RECV_DT
               < (select MAX(T2.RECV_DT) 
                    from Billa007.MAIN_TABLE T2
                   where
                         T2.policy_LOC = T1.policy_LOC
                     AND T2.POLICY_NO  = T1.POLICY_NO
                     AND T2.RECV_DT    <= CURRENT_DATE
                   GROUP BY
                         T2.policy_LOC
                       , T2.POLICY_NO
                 )
       ) d
)
, insert_report AS (
SELECT *
 FROM  FINAL TABLE
       (INSERT INTO Billa007.report_table
        SELECT policy_loc
             , policy_no
             , 'RHRESAER'
             , recv_dt
             , CURRENT_TIMESTAMP
             , 'DEL'
         FROM  delete_main
       )
)
SELECT *
 FROM  insert_report
;
------------------------------------------------------------------------------

POLICY_LOC   POLICY_NO PROGRAM_ID RECV_DT    LAST_TIMESTAMP             PROCESS_TYPE
------------ --------- ---------- ---------- -------------------------- ------------
AA                1234 RHRESAER   2011-10-19 2011-11-10-21.06.25.258000 DEL         
AA                2345 RHRESAER   2011-10-30 2011-11-10-21.06.25.258000 DEL         

  2 record(s) selected.


------------------------------ Commands Entered ------------------------------
SELECT * FROM Billa007.main_table;
------------------------------------------------------------------------------

POLICY_LOC   POLICY_NO PROGRAM_ID RECV_DT    LAST_TIMESTAMP            
------------ --------- ---------- ---------- --------------------------
AA                1234 FXTYUYER   2011-11-10 2011-10-13-12.46.54.411683
AA                2345 FXTYUYER   2011-11-10 2011-10-21-13.47.45.314682
BB                9999 FXTYUYER   2011-12-21 2011-09-19-12.46.54.411683

  3 record(s) selected.


------------------------------ Commands Entered ------------------------------
SELECT * FROM Billa007.report_table;
------------------------------------------------------------------------------

POLICY_LOC   POLICY_NO PROGRAM_ID RECV_DT    LAST_TIMESTAMP             PROCESS_TYPE
------------ --------- ---------- ---------- -------------------------- ------------
AA                1234 RHRESAER   2011-10-19 2011-11-10-21.06.25.258000 DEL         
AA                2345 RHRESAER   2011-10-30 2011-11-10-21.06.25.258000 DEL         

  2 record(s) selected.

Billa007 · #7 (**permalink**) 11-10-11, 07:41

Thanks for the effort;

The same error happened for me
"
SQL20165N An SQL data change statement within a FROM clause is not allowed
in the context in which it was specified. SQLSTATE=428FL

SQL20165N An SQL data change statement within a FROM clause is not allowed in the context in which it was specified.
"

tonkuma · #8 (**permalink**) 11-10-11, 07:54

If you received the error from my Example 1,
what DB2 Version, fixpack and Edition + your Operating System(including version info) are you using?

I used...

Code:

:\IBM\SQLLIB_V97\tools>db2level
DB21085I  Instance "DB2" uses "32" bits and DB2 code release "SQL09074" with 
level identifier "08050107".
Informational tokens are "DB2 v9.7.400.501", "s110330", "IP23237", and Fix Pack 
"4".
Product is installed at "D:\IBM\SQLLIB_V97" with DB2 Copy Name "DB2COPY1".

D:\IBM\SQLLIB_V97\tools>db2licm -l 
Product name:                     "DB2 Express-C"
License type:                     "Unwarranted"
Expiry date:                      "Permanent"
Product identifier:               "db2expc"
Version information:              "9.7"
Max number of CPUs:               "2"
Max amount of memory (GB):        "2"

If you tried my first example,
please try Example 1.

Billa007 · #9 (**permalink**) 11-10-11, 08:11

I tried in SPUFI tool also..error comes like below
"DSNT408I SQLCODE = -20165, ERROR: AN SQL DATA CHANGE STATEMENT WITHIN A FROM
CLAUSE IS NOT ALLOWED IN THE CONTEXT IN WHICH IT WAS SPECIFIED
DSNT418I SQLSTATE = 428FL SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXOP0 SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -100 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFF9C' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
"
Version is
DB2 SQL PRECOMPILER VERSION 9 REL. 1.0

tonkuma · #10 (**permalink**) 11-10-11, 09:46

Looking into manuals,
DB2 for z/OS seems more ristrictive about using data-change-table-reference.

in DB2 Version 9.1 for z/OS "Codes"

Quote:

-20165 AN SQL DATA CHANGE STATEMENT
WITHIN A FROM CLAUSE IS NOT
ALLOWED IN THE CONTEXT IN
WHICH IT WAS SPECIFIED

Explanation:
...
... The SQL data
change statement must be the only table-reference in
the FROM clause that is used in:
v the outer fullselect of a SELECT statement, that is a
subselect
v a SELECT INTO statement

while
in DB2 9.1 for LUW "Message Reference, Volume 2"

Quote:

SQL20165N An SQL data change statement within
a FROM clause is not allowed in the context
in which it was specified.

Explanation:
...
... The SQL data change statement must be the only
table-reference in the FROM clause that is used in:
v the outer fullselect of a SELECT statement
v a SELECT INTO statement
v the outer fullselect of a common table expression
v the only fullselect in an assignment statement.

DB2 for LUW includes support for "data change statement" in "the outer fullselect of a common table expression",
but DB2 for z/OS doesn't.

I don't know much about DB2 for z/OS than DB2 for LUW.
So, I'm sorry that I can't help you more.

My another idea is to use the delete statement as a table reference in a cursor, then fetch the cursor and insert into report_table,
though I don't know whather this idea work on DB2 for z/OS.

Billa007 · #11 (**permalink**) 11-11-11, 06:26

Thanks for the effort,

What i was trying is ..First performed the select query and trying to insert to the another table and do perform the delete process like below

Code:

Insert into Billa007.report_table
select from Billa007.MAIN_TABLE T1
         where T1.policy_LOC ='AA'
           and RECV_DT
               < (select MAX(T2.RECV_DT) 
                    from Billa007.MAIN_TABLE T2
                   where
                         T2.policy_LOC = T1.policy_LOC
                     AND T2.POLICY_NO  = T1.POLICY_NO
                     AND T2.RECV_DT    <= CURRENT_DATE
                   GROUP BY
                         T2.policy_LOC
                       , T2.POLICY_NO
                 )

and again do the perform the delete query..
but i am facing the below error

'SQL0412N Multiple columns are returned from a subquery that is allowed only
one column. SQLSTATE=42823 "

tonkuma · #12 (**permalink**) 11-11-11, 07:34

Is the code you showed realy what you executed?
Because, no column list in "select from Billa007.MAIN_TABLE T1".

Billa007 · #13 (**permalink**) 11-11-11, 07:48

Tried below query

Code:

Insert into Billa007.report_table(
POLICY_LOC
,POLICY_NO
,HOLDER_NAME
,HOLDER_AGE
,RECV_DT
,LAST_TIMESTAMP
,PROGRAM_ID
,PROCESS_TYPE)

select
 
POLICY_LOC
,POLICY_NO
,HOLDER_NAME
,HOLDER_AGE
,RECV_DT
,CURRENT_TIMESTAMP
,'RHRESAER'
, 'DEL'

  from Billa007.MAIN_TABLE T1
:
:
:

      , T2.POLICY_NO
                 )

Received error like below
SQLSTATE=22007

SQL0180N The syntax of the string representation of a datetime value is incorrect.

tonkuma · #14 (**permalink**) 11-11-11, 08:20

Why didn't you show full code without omission?

Anyhow, show the create table statements of Billa007.MAIN_TABLE and Billa007.report_table.

Billa007 · #15 (**permalink**) 11-11-11, 08:22

resolved the Datetime error..

please propose if any alternate way to insert the rows to another table whatever rows deleted in the table
Thanks again

Internet Services

Web Development

Digital Marketing

Consumer Tech