update 2 table, each table in diferent database using event schedule

Question

I have 2 databases, one of them is log, I want to make an event schedule in the main db but write a log in the db of logs

mainDB (event schedule doing something in mainDB and writing log in LOGDB)

LOGDB

I just don't know how to record data from one db event to another db

could someone tell me an example?

Answer 1

That is quite wage,

but you can do this

USE mainDB;
DELIMITER $$

CREATE EVENT e_daily
    ON SCHEDULE
      EVERY 1 DAY
    COMMENT 'explain here what has to be done each day'
    DO
      BEGIN
        DELETE FROM mainDB.mytable WHERE ID > 10;
        INSERT INTO LOGDB.mytable (time, total)
        VALUES (NOW(),10);

      END $$

DELIMITER ;

CEATE EVENT has some Restrictions that has to be observed.

The different schemas/Databses are addressed by writing the name of the database before a table name and add a dot like mainDB.mytable

The correct syntax of your queries should be tested, before starting an event.

Usually you make during testing, that it runs once or twice before ending, so that you can check the result.