delete from a table

aishvarya_82 · New User Joined: 11 Nov 2008 Posts: 11 Location: Pune

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.

cdhami · New User Joined: 24 Jan 2006 Posts: 28

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

GuyC · Posted: Mon Jul 12, 2010 1:10 pm

aishvarya_82 · New User Joined: 11 Nov 2008 Posts: 11 Location: Pune

Hi ,

Sorry for the late reply.

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

Thanks