OraFAQ Forum: SQL & PL/SQL » Delete data

Home » SQL & PL/SQL » SQL & PL/SQL » Delete data

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Delete data [message #354073]

Thu, 16 October 2008 06:15

amalesh2008
Messages: 7
Registered: September 2008
Location: New York

Junior Member

Hi All,

Can anybody help me in the following problem?

I have a table in which every day some data gets populated.
Now I need to keep only 12 weeks data in the table and delete the previous data, i.e., the recent 12 weeks data should be stored on the table at all times - not more than that.

What is the query to do this?

Thanks.

Report message to a moderator

Re: Delete data [message #354076 is a reply to message #354073]

Thu, 16 October 2008 06:26

joicejohn
Messages: 327
Registered: March 2008
Location: India

Senior Member

@amalesh2008,

Do you have a column which stores the date on which the data was entered?

Can't help much unless you post your table description.

Regards,
Jo

Report message to a moderator

Re: Delete data [message #354079 is a reply to message #354073]

Thu, 16 October 2008 06:39

amalesh2008
Messages: 7
Registered: September 2008
Location: New York

Junior Member

Hi joicejohn,

yesy i have a column which stores the date on which the data is entered in the table.

Thanks.

Report message to a moderator

Re: Delete data [message #354086 is a reply to message #354079]

Thu, 16 October 2008 06:45

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

Some statement like DELETE <table_name> WHERE <date_column> <= to_date('<date_to_delete_to','<format mask>'); would seem to be in order.

Report message to a moderator

Re: Delete data [message #354087 is a reply to message #354079]

Thu, 16 October 2008 06:45

joicejohn
Messages: 327
Registered: March 2008
Location: India

Senior Member

@amalesh2008,

So, Did you try to use that column for your requirement. Post what you have tried so far. Table description will be helpful

***Hint: Filter the records using your column and sysdate-(12*7)

Hope this helps

Regards,
Jo

Report message to a moderator

Re: Delete data [message #354089 is a reply to message #354073]

Thu, 16 October 2008 07:03

spmano1983
Messages: 269
Registered: September 2007

Senior Member

Hi,

Create a INSERT/UPDATE Trigger on your table. In the trigger write

delete from <your_table_name> where trunc(<date_column_name>) < trunc(sysdate)-84

Hope this will help you.

please correct me if am wrong.

Report message to a moderator

Re: Delete data [message #354100 is a reply to message #354073]

Thu, 16 October 2008 07:30

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

A trigger would be a highly inefficient and bug prone way of doing this.

Report message to a moderator

Re: Delete data [message #354101 is a reply to message #354073]

Thu, 16 October 2008 07:35

spmano1983
Messages: 269
Registered: September 2007

Senior Member

Hi cookiemonster,

Can yo please explain? inefficient in trigger then,
what idea you have to do this regularly???

Report message to a moderator

Re: Delete data [message #354102 is a reply to message #354073]

Thu, 16 October 2008 07:39

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

It's inefficient because you're running that delete statement everytime the table is modified but it'll only actually delete anything once a day (the truncs make sure of that).
If you want to run something like this regularly you should use dbms_job - it's what it's designed for.

Report message to a moderator

Re: Delete data [message #354103 is a reply to message #354073]

Thu, 16 October 2008 07:46

spmano1983
Messages: 269
Registered: September 2007

Senior Member

Hi,

Thanks for your info. But how can we schedule this???

Report message to a moderator

Re: Delete data [message #354109 is a reply to message #354073]

Thu, 16 October 2008 08:04

cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester

Senior Member

?
dbms_job is a scheduling tool

Report message to a moderator

Re: Delete data [message #354117 is a reply to message #354073]

Thu, 16 October 2008 08:15

amalesh2008
Messages: 7
Registered: September 2008
Location: New York

Junior Member

Hi all,

but is scheduling necessary?

bacause i'm inserting the data into the table through a stored procedure. In that same procedure, if I write
"DELETE FROM <table> where <date column> >= sysdate-(12*7)"

Report message to a moderator

Re: Delete data [message #354122 is a reply to message #354117]

Thu, 16 October 2008 08:22

joicejohn
Messages: 327
Registered: March 2008
Location: India

Senior Member

@amalesh2008,

I think that will do. (Avoid trigger as cookiemonster suggested) But careful with the condition though...

DELETE FROM <table> where <date column> >= sysdate-(12*7)

This will keep only the data that is prior to 12 weeks which means it will also delete the data you just inserted. Hope you don't want that right?

http://img2.mysmiley.net/imgs/smile/innocent/innocent0002.gif

Regards,
Jo

[Updated on: Thu, 16 October 2008 08:32]

Report message to a moderator

Re: Delete data [message #354158 is a reply to message #354103]

Thu, 16 October 2008 13:05

suneelreddy
Messages: 11
Registered: May 2008
Location: india

Junior Member

Hi ,

Query is

DELETE *
FROM emp
WHERE (SELECT SYSDATE - MIN(HireDate)
FROM emp) = 48;

Report message to a moderator

Re: Delete data [message #354160 is a reply to message #354158]

Thu, 16 October 2008 13:11

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

What question is this an answer to? Surely not the one at the top of this topic.

This might be the worst answer I have ever seen to a question.

Either it deleted no rows from the table or all rows from the table.

Report message to a moderator

Re: Delete data [message #354162 is a reply to message #354160]

Thu, 16 October 2008 13:31

joicejohn
Messages: 327
Registered: March 2008
Location: India

Senior Member

Reply to @suneelreddy,

I don't think it will do anything but give an error

http://img2.mysmiley.net/imgs/smile/winking/winking0001.gif

SQL> delete * from emp;
delete * from emp
       *
ERROR at line 1:
ORA-00903: invalid table name

Regards,
Jo

Report message to a moderator

Re: Delete data [message #354235 is a reply to message #354162]

Fri, 17 October 2008 02:31

suneelreddy
Messages: 11
Registered: May 2008
Location: india

Junior Member

Sorry there is no* it is only delete from ...

pls check

Report message to a moderator

Re: Delete data [message #354290 is a reply to message #354160]

Fri, 17 October 2008 07:11

joicejohn
Messages: 327
Registered: March 2008
Location: India

Senior Member

@suneelreddy,

Still, as joy_division suggested,

joy_division wrote on Thu, 16 October 2008 23:41

Either it deleted no rows from the table or all rows from the table.

Please understand the requirement of OP before posting your replies.

Regards,
Jo

Report message to a moderator

Previous Topic:	Oracle to Access file without using any utility Tool
Next Topic:	error ORA-01481

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Feb 06 19:39:45 CST 2025