CODEWITHSUNDEEP
sqloracle-database
DSD
DSD

Reputation: 601

Delete rows which are older than one day from oracle table

I have a table -

GENERIC_RECORDS (ENTRY_NO NUMBER, 
                 DATE_TIME VARCHAR2(25) , 
                 LOG_ID VARCHAR2(20), 
                 LOG_FILE_NAME VARCHAR2(200));

ENTRY_NO     DATE_TIME                  LOG_ID    LOG_FILE_NAME
1            2019-08-23 16:59:29.867    123       Log_temp1
2            2019-08-31 17:32:42.019    124       Log_temp2
3            2019-09-28 15:30:41.019    125       Log_temp3
4            2019-10-16 10:30:39.014    126       Log_temp4

I want to delete the records from this table which are older than 1 day. in above example only last record should remain

Thanks,

Upvotes: 0

Views: 313

Answers (2)

Geysa O. Marinho
Geysa O. Marinho

Reputation: 79

That would be my solution:

delete from GENERIC_RECORDS where to_date(substr(date_time,1,19), 'YYYY-MM-DD HH24:MI:SS') < sysdate;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269683

You can use delete with a where clause:

delete from generic_records
    where date_time < to_char(sysdate - interval '1' day, 'YYYY-MM-DD HH24:MI:SS')

I would recommend that you back the table up before trying this, just to be sure you don't cause permanent damage.

Upvotes: 1

Related Questions