| View previous topic :: View next topic |
| Author |
Message |
shyamsaravan
Beginner
Joined: 12 May 2010
Posts: 44
Topics: 12
Location: India
|
 Posted: Fri Nov 11, 2011 2:44 am Post subject: Deleted rows needs to insert into the table |
 |
|
Hi
I need to insert the multiple rows to the another table(A_TABLE) whatever row deleted in the below query
| Code: |
DELETE FROM P_TABLE T1
WHERE T1.PO_LC = 'AA'
AND INV_DT < (SELECT MAX(T2.INV_DT)
FROM P_TABLE T2
WHERE T2.PO_LC = T1.PO_LC
AND T2.PO_NO = T1.PO_NO
AND T2.INV_DT < CURRENT_DATE
GROUP BY T2.PO_LC
,T2.PO_NO)
|
Please help me on the INSERT query..
thanks |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Fri Nov 11, 2011 12:00 pm Post subject: |
|
|
shyamsaravan,
Assuming the inserting table has the same layout as P_table, try this untested sql
| Code: |
INSERT INTO INS_TABLE
SELECT *
FROM P_TABLE T1
WHERE T1.PO_LC = 'AA'
AND INV_DT < (SELECT MAX(T2.INV_DT)
FROM P_TABLE T2
WHERE T2.PO_LC = T1.PO_LC
AND T2.PO_NO = T1.PO_NO
AND T2.INV_DT < CURRENT_DATE
GROUP BY T2.PO_LC
,T2.PO_NO) ;
|
Code your DELETE statement right after the insert statement and commit them as single unit of work.
Kolusu |
|
| Back to top |
|
|
GuyC
Supermod
Joined: 27 May 2011
Posts: 35
Topics: 0
|
| Posted: Mon Nov 14, 2011 4:01 am Post subject: |
|
|
| or create a on-delete trigger on table T1 |
|
| Back to top |
|
|
shyamsaravan
Beginner
Joined: 12 May 2010
Posts: 44
Topics: 12
Location: India
|
| Posted: Mon Nov 14, 2011 7:28 am Post subject: |
|
|
Hi;
Thanks for the response;
Both table having almost similar columns along with distinct columns
P_TABLE column details
| Code: |
P_LOC
,P_NO
,H_NAME
,H_AGE
,M_TXT
,L_NAME
,P_ID
,INV_DT
,CRE_TIMESTAMP
|
INS_TABLE column details
| Code: |
P_LOC
,P_NO
,H_NAME
,H_AGE
,P_ID
,INV_DT
,INS_TIMESTAMP
,RESULT_TYPE
|
INS_TABLE record example | Code: |
P_LOC P_NO H_NAME H_AGE P_ID INV_DT INS_TIMESTAMP RESULT_TYPE
XX 4534 THOMOS 34 ERRESBER 2011-11-19 2011-11-10 15:47:54.511682 DEL |
In the INS_table will have the P_ID,RESULT_TYPE columns value as 'ERRESBER' and 'DEL' literals
INS_TIMESTAMP will have current time stamp |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12375
Topics: 75
Location: San Jose
|
| Posted: Mon Nov 14, 2011 11:25 am Post subject: |
|
|
shyamsaravan,
Would you ever post the complete details ever? How hard is to just change the select * to only the columns you need? The literals and current timestamp values should be easy.
try this untested sql
| Code: |
INSERT INTO INS_TABLE
SELECT P_LOC
,P_NO
,H_NAME
,H_AGE
,CHAR('ERRESBER')
,INV_DT
,CURRENT TIMESTAMP
,CHAR('DEL')
FROM P_TABLE T1
WHERE T1.PO_LC = 'AA'
AND INV_DT < (SELECT MAX(T2.INV_DT)
FROM P_TABLE T2
WHERE T2.PO_LC = T1.PO_LC
AND T2.PO_NO = T1.PO_NO
AND T2.INV_DT < CURRENT_DATE
GROUP BY T2.PO_LC
,T2.PO_NO) ;
|
Kolusu |
|
| Back to top |
|
|
shyamsaravan
Beginner
Joined: 12 May 2010
Posts: 44
Topics: 12
Location: India
|
| Posted: Tue Nov 15, 2011 2:54 am Post subject: |
|
|
Thank you so much..
I am performing the INSERT query first and performing the DELETE query in the program..like below..
PERFORM INSERT-PARA.
PERFORM DELETE-PARA.
| Code: |
INSERT-PARA.
EXEC SQL
INSERT INTO INS_TABLE
SELECT P_LOC
,P_NO
,H_NAME
:
:
END-EXEC
|
| Code: |
DELETE-PARA.
EXEC SQL
DELETE
FROM P_TABLE T1
WHERE T1.PO_LC = 'AA'
AND INV_DT < (SELECT MAX(T2.INV_DT)
:
:
END-EXEC
|
|
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
