MySQL scheduler job / event

In order to have MySQL event run, we need to make sure GLOBAL event_scheduler is set to on or 1.

SET GLOBAL event_scheduler = ON;

Or we can set in my.ini (in windows) or my.cnf (in linux) file with value

event_scheduler=on

Then create a scheduler job in MySQL. Example, i wish to call a stored procedure psp_DailyJob() every day start from 12:25:00.

CREATE EVENT CallDailyJob
ON SCHEDULE EVERY 1 DAY STARTS '2011-07-18 12:45:00'
DO
      CALL psp_DailyJob()

Search text in MySQL stored procedure

Search keyword or text in MySQL stored procedure

SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%IsConsignment%'
Order by Routine_Name;

You can't specify target table 'xx' for update in FROM clause

Try to update a column in MySQL which uses SUM and join and hit error "You can't specify target table 'xx' for update in FROM clause "

UPDATE tblstocktake ap SET TotalQuantity =
(
  SELECT SUM(t.Unitinstock)
  FROM tblstocktake t
  JOIN tblstocktake a
  ON a.productid = t.productid
  WHERE a.productid = ap.productid
  GROUP BY a.productid
);

Which is not working.

Finally, found a solution which is working

UPDATE tblstocktake LEFT JOIN
(SELECT productid,
SUM(unitinstock) AS baseVal
FROM tblstocktake
GROUP BY productid)
AS totalSum USING (productid)
SET totalquantity=baseVal;