MihirK98 MihirK98 - 3 months ago 7
MySQL Question

MySQL Query Syntax Error. Trying to create daily event but syntax error on simple query

I'm trying to create an event that is performed everyday at 00 HRS forever. I cannot figure out what syntax error I have made.
My code:

delimiter |

CREATE EVENT IF NOT EXISTS schedule_updation
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_DATE()
DO
ALTER TABLE `bookings` DROP COLUMN `Date1`;
ALTER TABLE `bookings` CHANGE `Date2` `Date1` TEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL;
ALTER TABLE `bookings` CHANGE `Date3` `Date2` TEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL;
ALTER TABLE `bookings` CHANGE `Date4` `Date3` TEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL;
ALTER TABLE `bookings` CHANGE `Date5` `Date4` TEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL;
ALTER TABLE `bookings` CHANGE `Date6` `Date5` TEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL;
ALTER TABLE `bookings` ADD `Date6` TEXT after `Date5`;

delimiter ;


The 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 'ALTER TABLE `bookings` CHANGE `Date2` `Date1` TEXT CHARACTER SET latin1 COLLATE ' at line 6


Database version:


  • Server: Local Databases (127.0.0.1 via TCP/IP)

  • Server type: MySQL

  • Server version: 5.7.11 - MySQL Community Server (GPL)

  • Protocol version: 10


Answer

While the column is not declared NOT NULL UNIQUE, there should be no problem. try

ALTER TABLE `bookings` CHANGE `Date2` `Date1` TEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL;

or

ALTER TABLE `bookings` CHANGE `Date2` `Date1` TEXT CHARACTER SET latin1 COLLATE latin1_swedish_ci null default null;