Question 231991 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL Run A Automatic script

I have a MySQL database with two tables in it: drivers and devices

The drivers table has a field called expiration date.
The devices table has a field called status.

My goal is to create a event that will :

  • Get current Date
  • Compare drivers (expiration date) with current date
  • If the expiration date is passed then it needs to change the status of the device to ‘EXP’

Is something like this possible?
It is like a expiration check

Tags:

2

Answers


  1. 0

    This is the query your event will need 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 about the creation of the event, you have multiple options, some of which are listed here:

    Whatever the case, you may want to wrap your UPDATE into a stored procedure and you may also want to wrap a transaction around the call of this event, especially if there are more write operations.

    Login or Signup to reply.
  2. 0

    Okay, so considering the following MySQL Table 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’d need to run through each driver, see if it has expired and update the device properly.

    First, you need to enable EVENTS in your database:

    SET GLOBAL event_scheduler = ON;

    Next, you can just create an event that runs each day, checks all the active devices for expired drivers and updates 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 ;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search