0

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?

Axxel
  • 45
  • 1
  • 10
  • Your query doesn't make any sense. What is that `ORDER BY` supposed to be doing? Are you sure that the code is even running? – Tim Biegeleisen Apr 30 '18 at 09:05
  • You are right about ORDER BY. I left it there by mistake, because before trying to select rows with latest datetime, I was trying to use ORDER BY and LIMIT. i know the code is not running, because his LAST_EXECUTED value in information_schema.events is NULL. – Axxel Apr 30 '18 at 09:09

0 Answers0