Create event in mysql with a query?

Question

I want to create an event in mysql, but want to add the sum and accumulated query that I put in the description

This is the event code, but I'm not sure how to do it:

CREATE EVENT `recurring data` 
  ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
  ON COMPLETION PRESERVE
  DO 
BEGIN
  INSERT INTO ....
END

this is the query:

SET @csum := 0, @product_id:=NULL;
   UPDATE sma_sale_items 
   SET acumulado = (@csum := if(product_id=@product_id,@csum, 00000.0000) + quantity), product_id=(@product_id:=product_id) 
   ORDER BY product_id, id, sale_id;

Update

I try to create this event, but it doesn't work, it tells me a mistake

1   CREATE EVENT `recurring data` 
2     ON SCHEDULE 
3     EVERY 1 MINUTE
4     STARTS CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
5   DO 
6     BEGIN
7     SET @csum := 0, @product_id:=NULL;
8     UPDATE sma_sale_items 
9     SET acumulado = (@csum := if(product_id=@product_id,@csum, 00000.0000) + quantity), product_id=(@product_id:=product_id) 
10    ORDER BY product_id, id, sale_id;
11    END

Error Code

#1064 - Something is wrong in its sintax near '' on line 7

I want to create an event in mysql, but add the sum and accumulated query that I put in the description — Kevin Gomez, Feb 06 '20 at 05:04
AFAIR UDV not allowed in event procedure. Use local variables instead of user-defined. — Akina, Feb 06 '20 at 19:05

Akina · Answer 1 · 2020-02-06T19:10:21.660

0

CREATE EVENT `recurring data` 
  ON SCHEDULE 
  -- how often it must be executed
  EVERY 1 MINUTE
  -- when the first execution must be performed
  STARTS CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
DO 
BEGIN
  -- perform the actions
END

Test

CREATE EVENT `recurring data` 
ON SCHEDULE 
EVERY 1 MINUTE
STARTS CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
DO 
    BEGIN
    DECLARE var_csum INTEGER DEFALUT 0;
    DECLARE var_product_id DECIMAL(10,4) DEFAULT NULL;
    UPDATE sma_sale_items 
    SET acumulado = (var_csum := if(product_id=var_product_id,var_csum, 00000.0000) + quantity), 
        product_id = (var_product_id:=product_id) 
    ORDER BY product_id, id, sale_id;
END

edited Feb 06 '20 at 19:10

answered Feb 06 '20 at 05:06

Akina

39,301
5
14
25

in -- perform the actions I put the query ? Just like I have it? – Kevin Gomez Feb 06 '20 at 05:24
@KevinGomez Yes, you put the queries which must be executed each minute instead of the comment. But you must understand that they consume the resources, so if these action queries are heavy then your server performance may degrade. And it is important to ensure that the actions takes less than 1 minute for to avoid of running the next schedule when previous one is not finished. – Akina Feb 06 '20 at 05:31
I try to create the event, but it sends me a mistake, by the query CREATE EVENT `recurring data` ON SCHEDULE EVERY 1 MINUTE STARTS CURRENT_TIMESTAMP + INTERVAL 1 MINUTE DO BEGIN SET @csum := 0, @product_id:=NULL; UPDATE sma_sale_items SET acumulado = (@csum := if(product_id=@product_id,@csum, 00000.0000) + quantity), product_id=(@product_id:=product_id) ORDER BY product_id, id, sale_id; END – Kevin Gomez Feb 06 '20 at 18:22
try the new one, but I this mistake #1064 - Something is wrong in its sintax near '' on line 7 – Kevin Gomez Feb 06 '20 at 20:00
@KevinGomez Do you remember about DELIMITER reassign? – Akina Feb 06 '20 at 21:06

Create event in mysql with a query?

Update

1 Answers1