Home » SQL & PL/SQL » SQL & PL/SQL » Delete data
Delete data [message #354073] Thu, 16 October 2008 06:15 Go to next message
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.
Re: Delete data [message #354076 is a reply to message #354073] Thu, 16 October 2008 06:26 Go to previous messageGo to next message
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
Re: Delete data [message #354079 is a reply to message #354073] Thu, 16 October 2008 06:39 Go to previous messageGo to next message
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.
Re: Delete data [message #354086 is a reply to message #354079] Thu, 16 October 2008 06:45 Go to previous messageGo to next message
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.
Re: Delete data [message #354087 is a reply to message #354079] Thu, 16 October 2008 06:45 Go to previous messageGo to next message
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
Re: Delete data [message #354089 is a reply to message #354073] Thu, 16 October 2008 07:03 Go to previous messageGo to next message
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.
Re: Delete data [message #354100 is a reply to message #354073] Thu, 16 October 2008 07:30 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
A trigger would be a highly inefficient and bug prone way of doing this.
Re: Delete data [message #354101 is a reply to message #354073] Thu, 16 October 2008 07:35 Go to previous messageGo to next message
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???
Re: Delete data [message #354102 is a reply to message #354073] Thu, 16 October 2008 07:39 Go to previous messageGo to next message
cookiemonster
Messages: 12422
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.
Re: Delete data [message #354103 is a reply to message #354073] Thu, 16 October 2008 07:46 Go to previous messageGo to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Hi,

Thanks for your info. But how can we schedule this???
Re: Delete data [message #354109 is a reply to message #354073] Thu, 16 October 2008 08:04 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
?
dbms_job is a scheduling tool
Re: Delete data [message #354117 is a reply to message #354073] Thu, 16 October 2008 08:15 Go to previous messageGo to next message
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)"
Re: Delete data [message #354122 is a reply to message #354117] Thu, 16 October 2008 08:22 Go to previous messageGo to next message
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 Go to previous messageGo to next message
suneelreddy
Messages: 11
Registered: May 2008
Location: india
Junior Member
Hi ,

Query is

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

Re: Delete data [message #354160 is a reply to message #354158] Thu, 16 October 2008 13:11 Go to previous messageGo to next message
joy_division
Messages: 4644
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.
Re: Delete data [message #354162 is a reply to message #354160] Thu, 16 October 2008 13:31 Go to previous messageGo to next message
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
Re: Delete data [message #354235 is a reply to message #354162] Fri, 17 October 2008 02:31 Go to previous messageGo to next message
suneelreddy
Messages: 11
Registered: May 2008
Location: india
Junior Member
Sorry there is no* it is only delete from ...


pls check
Re: Delete data [message #354290 is a reply to message #354160] Fri, 17 October 2008 07:11 Go to previous message
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

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


Current Time: Fri Dec 09 21:44:16 CST 2016

Total time taken to generate the page: 0.22709 seconds