CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Peter Penzov
Peter Penzov

Reputation: 1754

Delete rows older than 1 month

I have this Oracle table which I would like to delete rows if they are older than 1 month?

CREATE TABLE EVENTS(
  EVENTID INTEGER NOT NULL,
  SOURCE VARCHAR2(50 ),
  TYPE VARCHAR2(50 ),
  EVENT_DATE DATE,
  DESCRIPTION VARCHAR2(100 )
)
/

Also how I can delete rows of the total size of the table is for example 100 MB?

Upvotes: 0

Views: 895

Answers (1)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50077

You should be able to do something like

DELETE FROM EVENTS
  WHERE EVENT_DATE < ADD_MONTHS(SYSDATE, -1);

Best of luck.

EDIT (see OP's comment below regarding XML)

Without knowing how you're obtaining or using the XML value it's hard to say but perhaps something like 

DELETE FROM EVENTS
  WHERE EVENT_DATE < ADD_MONTHS(SYSDATE, XMLvalue * -1);

where XMLvalue is the value you get from the XML.

Best of luck.

Upvotes: 2

Related Questions