MYSQL DATE_ADD interval as a variable OR from another column

Question

I am trying to create an event on my table to run every few minutes to see if 'next_run' is now() and if yes add 'frequency' to 'next_run', but I am receiving syntax exception every time i do.

below is the table structure...

     LAST_RUN       |FREQUENCY|   NEXT_RUN 
----------------------------------------------------
2016-09-15 06:02:06 | 1 DAY   | 2016-09-15 06:02:06

" and the code i am using is this...

UPDATE TASKS_MASTER_COPY 
@num:=CAST(FREQUENCY) AS UNSIGNED,
@p  :=SUBSTR(FREQUENCY, CHAR_LENGTH(@num)+2)
LAST_RUN=NEXT_RUN,
NEXT_RUN=NEXT_RUN + CASE
        WHEN @p='YEAR' THEN DATE_ADD(NEXT_RUN, INTERVAL @num YEAR)
        WHEN @p='MONTH' THEN DATE_ADD(NEXT_RUN, INTERVAL @num MONTH)
        WHEN @p='DAY' THEN DATE_ADD(NEXT_RUN, INTERVAL @num DAY)
        WHEN @p='WEEK' THEN DATE_ADD(NEXT_RUN, INTERVAL @num WEEK)
            END
WHERE TASK_MASTER_ID=100;

Can someone please help me with this? Thanks in advance.

/* SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@num:=CAST(FREQUENCY) AS UNSIGNED, @p :=SUBSTR(FREQUENCY, CHAR_LENGTH(@num)+2)' at line 2 */ /* Affected rows: 0 Found rows: 0 Warnings: 0 Duration for 0 of 1 query: 0.000 sec. */ — Ram, Sep 19 '16 at 17:31

score 0 · Answer 1 · edited May 23 '17 at 12:00

This version does not error for me:

UPDATE TASKS_MASTER_COPY
SET
LAST_RUN = NEXT_RUN,
NEXT_RUN = NEXT_RUN +
  CASE
    WHEN SUBSTR(FREQUENCY, CHAR_LENGTH(CAST(FREQUENCY AS UNSIGNED)) + 2) = 'YEAR'
    THEN DATE_ADD(NEXT_RUN, INTERVAL CAST(FREQUENCY AS UNSIGNED) YEAR )
    WHEN SUBSTR(FREQUENCY, CHAR_LENGTH(CAST(FREQUENCY AS UNSIGNED)) + 2) = 'MONTH'
    THEN DATE_ADD(NEXT_RUN, INTERVAL CAST(FREQUENCY AS UNSIGNED) MONTH)
    WHEN SUBSTR(FREQUENCY, CHAR_LENGTH(CAST(FREQUENCY AS UNSIGNED)) + 2) = 'DAY'
    THEN DATE_ADD(NEXT_RUN, INTERVAL CAST(FREQUENCY AS UNSIGNED) DAY  )
    WHEN SUBSTR(FREQUENCY, CHAR_LENGTH(CAST(FREQUENCY AS UNSIGNED)) + 2) = 'WEEK'
    THEN DATE_ADD(NEXT_RUN, INTERVAL CAST(FREQUENCY AS UNSIGNED) WEEK )
  END
WHERE TASK_MASTER_ID = 100;

A few issues:

CAST(FREQUENCY AS UNSIGNED) not CAST(FREQUENCY) AS UNSIGNED
An UPDATE needs a SET after the table name
I believe an UPDATE can only contain the columns that are going to be updated and can not include @num and @p.

Based on https://stackoverflow.com/a/22404258/761771

MYSQL DATE_ADD interval as a variable OR from another column

1 Answers1