SQL syntax error

Question

I'm trying to execute this query:

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.columns 
WHERE table_name = 'MyTableName' AND column_name = 'ColumnInQuestion')
THEN ALTER TABLE MyTableName DROP COLUMN ColumnInQuestion;

And I get the following error message:

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 'IF EXISTS(SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'articles'' at line 1

I'm using MySQL 5.1.something, so I don't think information_schema not being defined is the issue. Any ideas?

Thank you for your help!

@OMG Ponies: it doesn't matter how he runs it. In this query mysql tries to use DML IF in the context of DDL (at least I think so). According to the documentation - it is not possible to write such conditional DDLs. — zerkms
– zerkms, Commented Nov 30, 2010 at 2:06

marc_s · Accepted Answer · 2017-01-25 16:55:07Z

2

You cannot do this. IF EXISTS is meaningful only for creating tables/databases

If you need to drop column if it exists - you could use

ALTER IGNORE TABLE DROP COLUMN ...

Note on IGNORE.

edited Jan 25, 2017 at 16:55

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Nov 30, 2010 at 1:49

zerkms

256k73 gold badges447 silver badges551 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ike Walker · Accepted Answer · 2010-11-30 19:25:38Z

You have a couple of options.

One option is to just run the ALTER TABLE command and ignore the error if the column doesn't exist.

Another option closer in spirit to your original attempt is to conditionally dump the DDL to a SQL script on disk if the column exists, then source that SQL script to run the DDL (if any).

Here's an example:

-- delete the SQL script if it exists
\! rm /tmp/drop_column.sql

-- Conditionally dump the DDL to a SQL script if the column exists
SELECT concat('ALTER TABLE ',table_schema,'.',table_name,
' DROP COLUMN ',column_name,';') as sql_stmt
into outfile '/tmp/drop_column.sql'
FROM INFORMATION_SCHEMA.columns 
WHERE table_name = 'MyTableName' AND column_name = 'ColumnInQuestion';

-- execute the SQL script
\. /tmp/drop_column.sql

-- delete the SQL script from disk
\! rm /tmp/drop_column.sql

Collectives™ on Stack Overflow

SQL syntax error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related