Event creating with 2 SQL statements

[designtrooper]

I am trying to create an event with two SQL statements in it.I read the docs herehttps://dev.mysql.com/doc/refman/5.7/en/create-event.html

This is the syntax I used:

delimiter |
CREATE EVENT empty_avail_apps_table
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 5 minute
DO 
BEGIN
DELETE FROM avail_apps;
NSERT INTO avail_apps (date_av,time_av)
values(5,6);
END |
delimiter;

I am trying here to empty a table and then insert some new values in it.
So…my problem is that only the first command is executed(the delete).

What Am I doing wrong?

[Nelson]

Try:

CREATE EVENT empty_avail_apps_table
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 5 minute
DO 
BEGIN
DELETE FROM avail_apps;
INSERT INTO avail_apps (date_av,time_av) VALUES (5,6);
END

[designtrooper]

I got a warning about syntax.

error code 1064, SQL state 42000: 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 'END' at line 1

[molona]

In the example they don’t use BEGIN or END so try this, just to check if you get the same result

CREATE EVENT empty_avail_apps_table
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 5 minute
DO 
DELETE FROM avail_apps;
INSERT INTO avail_apps (date_av,time_av) VALUES (5,6);

[designtrooper]

I tried this query(I just changed the format of the inserted values to be according to the avail_apps table):

CREATE EVENT empty_avail_apps_table
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 5 minute
DO 
DELETE FROM avail_apps;
INSERT INTO avail_apps (date_av,time_av) VALUES (2016-07-15,09:00:00);

but I get this warning regarding the values to be inserted.
error code 1064, SQL state 42000: 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 ‘:00:00)’ at line 1

Is it certain that BEGIN and END are not required?
Here is the show create statement for table avail_apps;

CREATE TABLE avail_apps (
id int(11) NOT NULL AUTO_INCREMENT,
date_av date NOT NULL,
time_av time DEFAULT NULL,
PRIMARY KEY (id,date_av)
) ENGINE=InnoDB AUTO_INCREMENT=9883 DEFAULT CHARSET=utf8

[r937]

date and time values need to be delimited

change this –

VALUES(2016-07-15,09:00:00);

to this –

VALUES('2016-07-15','09:00:00');

p.s. why two columns instead of a single DATETIME column?

[designtrooper]

The query finally works.

Regarding the two columns for date and time:
It has to do with the app my building,I do not remember the reasons though right now.

[designtrooper]

there is one last (important) detail…
In the example above the values to be inserted are fixed.

The SQL statements contained in the EVENT must run every every 7 months(that is the requirement of the app I am building) .As such the values to be inserted must be different each time.

How I could accomplish these two things…I do not thing the first is difficult,there must be a CLAUSE/KEYWORD that will automate the queries running every 7 months.

But the second…?