0

Afternoon,

I am trying to figure out why this code would work as a standalone SQL statement but does not work in the "Events" tab - Using PhpMyAdmin to set up an event, first time attempting this so probably a very easy answer, excuse my simple mind!

##First Statement - create temporary table because insert will not allow us to select from same table!

CREATE TABLE temporary_calendar
SELECT * FROM calendar;

## Second Statement - carry out insert, take latest date from `temporary_calendar` and then iterate it by one day and insert it back in to `calendar`

INSERT IGNORE INTO calendar 
(id, date_for, capacity, booked, remaining)
VALUES 
(null, (SELECT DATE_ADD((SELECT date_for FROM temporary_calendar ORDER BY date_for DESC LIMIT 1), INTERVAL 1 
DAY)), (SELECT no_of_desks_bookable FROM parameters LIMIT 1), 0, (SELECT no_of_desks_bookable FROM 
parameters LIMIT 1));

## Third Statement - drop the `temporary_calendar` table
DROP TABLE temporary_calendar;

I've ran this in the SQL tab and it runs fine and creates a new date. But when I run in the Event I created I get the following generic error:

enter image description here

Dan W
  • 365
  • 1
  • 4
  • 20
  • Looks like multi-query not supported or delimiter lost. *But when I run in the Event* Show complete CREATE EVENT code. – Akina Sep 18 '20 at 12:28
  • 1
    IF you have more than 1 statement in a stored program(and an event is a stored program) then you need to enclose them in a BEGIN..END block (and set delimiters) – P.Salmon Sep 18 '20 at 12:55
  • Thanks @P.Salmon that seems to have resolved it. I think part of the issue was using the phpMyAdmin GUI because it generates those bits of the code for you so you can not see them! I ran the whole create in the SQL tab and no error :). – Dan W Sep 21 '20 at 08:27

0 Answers0