Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

delete from a table

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
aishvarya_82

New User


Joined: 11 Nov 2008
Posts: 11
Location: Pune

PostPosted: Mon Jul 12, 2010 12:32 pm    Post subject: delete from a table
Reply with quote

Hello,

I have two tables 1) log table 2) user table

log table is having user id ,actvty_timestamp
and user table is having user id and retention days as column

my job is to delete the records from log table if the following condition
satisfies

1)current timestamp - actvty_timestamp > retention days
2)and userid should match of both the tables

I have written the following query but it is not working fine.

DELETE FROM SDAUDLOG A WHERE A.USER_ID IN
(SELECT A.USER_ID
FROM DEVSQL3.COMISDFT B,DEVSQL3.SDAUDLOG A
WHERE B.USERID=A.USER_ID
AND (DAY(CURRENT_TIMESTAMP)-DAY(A.ACTVTY_TS))>B.ACTV_LOG_RETN_DYS );

it is deleting all the records related to the userid .But i don't want to delete all the records.I have to delete the records satisfying the two conditions only

please suggest.Thanks.
Back to top
View user's profile Send private message

cdhami

New User


Joined: 24 Jan 2006
Posts: 28

PostPosted: Mon Jul 12, 2010 1:08 pm    Post subject:
Reply with quote

Hi Aishvarya,
it is deleting records related to user id because you are putting a check only on the matched user id before delete.

I suggest to use a existence check instead of subquery which will satisfy your requirement.


Hope this helps.
cdhami
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1280
Location: Belgium

PostPosted: Mon Jul 12, 2010 1:10 pm    Post subject:
Reply with quote

Code:
DELETE FROM SDAUDLOG A
WHERE A.ACTVTY_TS < (select current_timestamp - B.ACTV_LOG_RETN_DYS  Days from  DEVSQL3.COMISDFT B WHERE B.USERID=A.USER_ID)
Back to top
View user's profile Send private message
aishvarya_82

New User


Joined: 11 Nov 2008
Posts: 11
Location: Pune

PostPosted: Wed Jul 21, 2010 9:06 pm    Post subject: Reply to: delete from a table
Reply with quote

Hi ,

Sorry for the late reply.

The query is running fine now as answered in this post.
Thank you very much

Thanks
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Data replication from multiple Db2 ta... kishpra DB2 5 Mon Mar 27, 2017 9:58 pm
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm
No new posts Join of more than one table dibyendumandal11111 DB2 6 Mon Feb 13, 2017 11:36 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
No new posts JCL to delete component in a package sundaram.naveen Compuware & Other Tools 14 Tue Nov 29, 2016 6:21 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us