CODEWITHSUNDEEP
mysqlsqldatabaseauto-delete
mrobbizulfikar
mrobbizulfikar

Reputation: 461

AUTO-DELETE ROW 1 Day After Inserted (MySQL)

How to create event Auto-Delete some row 1 day after inserting that row?

I have 3 fields:

Upvotes: 3

Views: 9188

Answers (3)

Mike
Mike

Reputation: 195

Please take a look at Event: https://dev.mysql.com/doc/refman/5.7/en/events-syntax.html in your case: you can try this one:

-- create a timer to update this table automatically

DROP EVENT IF EXISTS `et_update_your_trigger_name`;
CREATE EVENT `et_update_your_trigger_name`  ON SCHEDULE EVERY 1 MINUTE 
STARTS '2010-01-01 00:00:00' 
DO 
DELETE FROM `DB_NAME`.`table_name` where DATEDIFF(now(),`timestamp`) > 1;

ALTER EVENT `et_update_your_trigger_name` ON  COMPLETION PRESERVE ENABLE;

this sql code create a trigger, and execute every minutes.

Upvotes: 7

PHP Geek
PHP Geek

Reputation: 4033

Please try like this:

DELETE FROM table1
WHERE date < DATE_SUB(NOW(), INTERVAL 1 DAY)

Upvotes: 0

Ed Bangga
Ed Bangga

Reputation: 13006

Here's your scripts.

Insert into table1 (id, name, timestamp) values (1, 'test', now())

after insert

Delete from table1 WHERE timestamp < now() - interval 1 day;

Upvotes: 4

Related Questions