I have a MySQL table table1 structured like this:
idTable1, datetime, field1, field2
and I want to set an event that runs every 5 minutes and copies in table2 the last rows having distinct couples of (field1, field2) and dateandtime being the most recent.
For example, if now it was 04/30/2018 10:36:50 and in table1 there were these rows
idTable1| field1 | dateandtime | field2 |
--------------------------------------------------------------
|1 | value1field1 |2018-04-30 10:30:07 | value1field2 |
--------------------------------------------------------------
|2 | value2field1 |2018-04-30 10:32:41 | value1field2 |
--------------------------------------------------------------
|3 | value1field1 |2018-09-03 10:33:17 | value1field2 |
--------------------------------------------------------------
|4 | value3field1 |2018-09-03 10:35:27 | value1field2 |
--------------------------------------------------------------
|5 | value1field1 |2018-09-03 10:36:17 | value1field2 |
--------------------------------------------------------------
I want the event to copy in table2 these data:
idTable2 |idTable1| field1 | dateandtime | field2 |
--------------------------------------------------------------------------
|1 |2 | value2field1 |2018-04-30 10:32:41 | value1field2 |
--------------------------------------------------------------------------
|2 |3 | value1field1 |2018-09-03 10:33:17 | value1field2 |
--------------------------------------------------------------------------
|3 |5 | value1field1 |2018-09-03 10:36:17 | value1field2 |
--------------------------------------------------------------------------
I tried the following code but it doesn't seem to insert any record:
BEGIN
INSERT INTO table2 (idTable1, field1, dateandtime, field2)
SELECT idTable1, field1, dateandtime, field2
FROM table1
WHERE dateandtime > date_sub(now(), interval 5 MINUTE)
GROUP BY field1, field2
ORDER BY dateandtime DESC;
END
Where am I wrong?