Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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: 1281
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 Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts Table(Unicode(Graphic) table) loading... muralikrishnan_new DB2 0 Thu Oct 05, 2017 5:10 pm
No new posts Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm
No new posts how to see when the last read access ... Mike 1304 DB2 1 Tue Sep 12, 2017 7:52 pm
No new posts PC (UTF-8) -> z/OS (EBCDIC) -> ... prino All Other Mainframe Topics 4 Fri Sep 01, 2017 1:47 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us