How to schedule a MySQL query?

by

you have 2 basic options (at least):

1, Take a look at Event Scheduler

First create table eg. stock_dumps with fields

itemcode, quantity, avgcost, ttlval,dump_date (DATETIME)

CREATE EVENT `Dumping_event` ON SCHEDULE
        EVERY 1 DAY
    ON COMPLETION NOT PRESERVE
    ENABLE
    COMMENT ''
    DO BEGIN
INSERT INTO stock_dumps(itemcode, quantity, avgcost, ttlval,dump_date)
SELECT itmcode, quantity, avgcost, (avgcost * quantity)as ttlval, NOW()
  FROM table_1 JOIN table_2 ON table_1.itmcode = table_2.itmcode;
END

Please follow instructions how to enable scheduler on link posted above.
Note : Old versions of mysql don’t have event scheduler

2, Create cron job/windows scheduled job:

create sql file:

INSERT INTO stock_dumps(itemcode, quantity, avgcost, ttlval,dump_date)
SELECT itmcode, quantity, avgcost, (avgcost * quantity)as ttlval, NOW()
FROM table_1 JOIN table_2 ON table_1.itmcode = table_2.itmcode;

schedule this command:

mysql -uusername -ppassword < /path/to/sql_file.sql

More Related Contents:

  1. How do I specify unique constraint for multiple columns in MySQL?
  2. Select all columns except one in MySQL?
  3. MYSQL OR vs IN performance
  4. ERROR 1452: Cannot add or update a child row: a foreign key constraint fails
  5. MySQL Conditional Insert
  6. grant remote access of MySQL database from any IP address
  7. Should MySQL have its timezone set to UTC?
  8. Is there a MySQL option/feature to track history of changes to records?
  9. MySQL JOIN ON vs USING?
  10. SQL – Update multiple records in one query
  11. How to store IPv6-compatible address in a relational database
  12. MySQL: View with Subquery in the FROM Clause Limitation
  13. Select data between a date/time range
  14. auto increment primary leaving gaps in counting
  15. OUTPUT Clause in MySQL
  16. Relationship between catalog, schema, user, and database instance
  17. Sorting by date & time in descending order?
  18. passing LIMIT as parameters to MySQL sproc
  19. What is the difference between BIT and TINYINT in MySQL?
  20. MySQL ERROR 1290 (HY000) –secure-file-priv option
  21. Can’t connect to MySQL server on ‘127.0.0.1’ (10061) (2003)
  22. MySQL, Concatenate two columns
  23. How to only select numeric data from mysql?
  24. What is the MySQL query equivalent of PHP strip_tags?
  25. Is it Possible to Enforce Data Checking in MySQL using Regular expression
  26. How to select data where a field has a min value in MySQL?
  27. ALTER TABLE ADD COLUMN takes a long time
  28. Creating MYSQL Procedure in Laravel 4 Migrations
  29. MySQL: Computed Column
  30. Mysql server does not support 4-byte encoded utf8 characters

Leave a Comment