Menu
I want to delete all the records which is older than 24 hours. I am using the below query for the same but sometimes doesn't run perfectly. I want to know whether my query is right or wrong ? Or which is better way to do so.
delete from TEMP_SERVICE_OPTION where EVENT_DATE < TRUNC(SYSDATE) - 1;
953
On large deletes, it is often more efficient to create a new table (create table as select) with the non deleted results, and migrate metadata (constraints, indexes, triggers etc.), drop old table and rename new table to existing name.
You could use IN : DELETE FROM MYTABLE WHERE (ID, NAME) IN (SELECT 1 AS ID, 'xyz' AS NAME FROM dual UNION ALL SELECT 2 AS ID, 'abc' AS NAME FROM dual UNION ALL SELECT 3 AS ID, 'abc' AS NAME FROM dual); Of course inside subquery you could use any select (for instance from global temporary table). Save this answer.
If you want older than 24 hours then do:
where event_date < sysdate - 1
If you want before yesterday, then do:
where event_date < trunc(sysdate) - 1
As for performance, that depends on how many rows are being deleted. If your table only has thousands of rows, then this is fine. If it has millions of rows, then you might want an index on event_date. Or, you might even want to take a different approach -- selecting the data into a temporary table, truncating the original table, and then re-inserting it.
68
Your query is deleting records where the EVENT_DATE is before yesterday.
If you want to delete records that are over 24 hours old, try:
delete from TEMP_SERVICE_OPTION where (SYSDATE - EVENT_DATE) > 1;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
