How to use Event Scheduler based on two tables?-CodePudding

CREATE EVENT sensor_checker
ON SCHEDULE EVERY 15 MINUTE
DO
UPDATE tb_device d 
inner join arduino_id a 
on d.id_device = a.id_node
SET d.status_device = 0 WHERE ADDTIME(new.date_server, '900') < now()

So I have two tables tb_arduino and tb_device, I am trying to create an event that when column 'date_server' (from tb_arduino table) is less then 15 minute from current time the column 'status_device'(from tb_device) will update the value of the column 'status_device' to 0 else the column update the value to 1. But the query above doesn't seems to work.

More explanation:

If the current time is 12:20:40

'tb_arduino' table

| id_node  | luminance | date_server           |
| -------- | --------  | --------              |
| s01      | 270       | 2023-27-01 12:20:30   |
| s01      | 280       | 2023-27-01 12:10:30   |
| s02      | 210       | 2023-27-01 12:00:30   |
| s02      | 230       | 2023-27-01 11:55:30   |

Then the value 'status_device' of 'id_node' s02 is become 0

'tb_device' table

| id_device | status_device |
| --------  | --------      |
| s01       | 1             |
| s02       | 0             |

CodePudding user response：

Test this:

CREATE EVENT sensor_checker
ON SCHEDULE EVERY 15 MINUTE
DO
UPDATE tb_device
SET status_device = EXISTS (
    SELECT NULL
    FROM arduino_id 
    WHERE tb_device.id_device = arduino_id.id_node
      AND arduino_id.date_server >= NOW() - INTERVAL 15 MINUTE
    );

Of course firstly test UPDATE query separately.