0

I'm trying to ALTER a new column IF NOT EXITS. I modified the code of the third answer of this question:

DELIMITER $$

DROP PROCEDURE IF EXISTS myUpgrade $$
CREATE PROCEDURE myUpgrade()
BEGIN

IF NOT EXISTS
    ((SELECT * FROM information_schema.COLUMNS WHERE `TABLE_SCHEMA` = DATABASE() AND`COLUMN_NAME` = 'myNewColumn' `TABLE_NAME` = 'my_table'))
THEN
    ALTER TABLE `my_table` ADD `myNewColumn` VARCHAR( 255 ) NOT NULL AFTER `id`;
ENDIF;

END $$

CALL myUpgrade() $$

DELIMITER;

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 '`TABLE_NAME` = 'my_table'))
THEN
    ALTER TABLE `my_table` ADD `myNewColumn` VA' at line 5

Maybe useful:

  • I run the code in PHPMyAdmin (4.0.10deb1)
  • Server version: 5.5.38-0ubuntu0.14.04.1
  • Protocol-Version: 10
  • Apache/2.4.7
  • Database-Client version: libmysql - 5.5.38
  • PHP-Extension: mysqli

Thanks in advance!

Community
  • 1
  • 1
Mr. B.
  • 8,041
  • 14
  • 67
  • 117

2 Answers2

1

You're missing an AND before the TABLE_NAME condition in the WHERE clause:

IF NOT EXISTS
    ((SELECT * FROM information_schema.COLUMNS 
      WHERE `TABLE_SCHEMA` = DATABASE() 
        AND `COLUMN_NAME` = 'myNewColumn'
        AND `TABLE_NAME` = 'my_table'))

Also, there needs to be a space in END IF.

Barmar
  • 741,623
  • 53
  • 500
  • 612
1

I think you are missinhg AND 'myNewColumn' and TABLE_NAME

That particular part should look like this:

((SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() ANDCOLUMN_NAME = 'myNewColumn' AND TABLE_NAME = 'my_table'))

blagi
  • 26
  • 4