This is the query your event needs to execute:

UPDATE devices SET `status` = 'EXP' WHERE EXISTS ( SELECT 1 FROM drivers JOIN device_drivers ON drivers.id = device_drivers.driver_id AND device_drivers.device_id = devices.id WHERE drivers.`expiration date` < now() );

As for event creation, you have several options, some of which are listed here:

• Event Scheduler(Thanks @Akina)
• cron job
• Heartbeat Mode
• There could even be some UI or other events that trigger a call to this

Regardless, you may want to wrap yourUPDATEin astored procedure, and if there are more writes, you may also want to call this event when Wrap atransaction.

Okay, consider the followingMySQLtable structure:

CREATE TABLE Devices ( device_id INT PRIMARY KEY, status ENUM('ACTIVE', 'EXPIRED') ); CREATE TABLE Drivers ( driver_id INT PRIMARY KEY, device_id INT, expiration_date DATE, FOREIGN KEY (device_id) REFERENCES Devices(device_id) );

You need to go through each driver to see if it has expired and update the device correctly.

First, you need to enableEVENTSin the database:

SET GLOBAL event_scheduler = ON;

Next, you can create aneventthat runs daily, checking allactivedevices forexpireddrivers and updating them appropriately:

DELIMITER // CREATE EVENT UpdateDeviceStatus ON SCHEDULE EVERY 1 DAY STARTS CURRENT_DATE DO BEGIN UPDATE Devices d SET d.status = 'EXPIRED' WHERE EXISTS ( SELECT 1 FROM Drivers dr WHERE dr.device_id = d.device_id AND dr.expiration_date < CURRENT_DATE ) AND d.status = 'ACTIVE'; END; // DELIMITER ;