How to execute a query interval of 1 month in mysql

Question

I am developing an application using servlet and jdbc using mysql database.

Here I have to move one table data into other table in every month end(lets say 30th)

visitorlog

id name date

1 XYZ 02-10-2016

visitorloghistory

id name date

Here I have to move all data of visitorlog into visitorloghistory in each month end, and need to remove data from visitorlog.

I have no idea how to do this.

Thanks in advance.

AT-2017 · Accepted Answer · 2016-08-30T10:40:19.360

3

This is called event scheduler or more specifically cron job. The following should help you to start:

https://www.sitepoint.com/how-to-create-mysql-events/

http://www.mysqltutorial.org/mysql-triggers/working-mysql-scheduled-event/

The event schedulers are set to perform specific tasks depending upon time. The following is a sample:

DELIMITER ;;
  CREATE EVENT UpdateData ON SCHEDULE EVERY DAY STARTS '2016-10-10 00:00:00' -- This is scheduled to start from '2016-10-10' and updates data every day
  DO BEGIN
    UPDATE table1 SET Status = 1 WHERE Status = 0;
    UPDATE table2 SET Status = 1 WHERE Status = 0;
  END;;
DELIMITER ;

edited Aug 30 '16 at 10:40

answered Aug 30 '16 at 10:18

AT-2017

3,114
3
23
39

Where i have to write such query in some batch file or in servlet program. – Mickey Patel Aug 30 '16 at 10:26
This should be done in the MySQL server. – AT-2017 Aug 30 '16 at 10:36

score 0 · Answer 2 · edited May 23 '17 at 11:53

0

Your can check this out. Write a script which will run at particular time.

Cron job for a Java Program

edited May 23 '17 at 11:53

Community

1
1

answered Aug 30 '16 at 10:27

Jagrati

11,474
9
35
56

How to execute a query interval of 1 month in mysql

2 Answers2