Alter ignore table add column if not exists using the SQL statement

Question

i want to add a new column to a mysql table, but i want to ignore the adding of column, if the column already exists

i am currently using

ALTER IGNORE TABLE `db`.`tablename` ADD COLUMN `column_name` text NULL;

but this is throwing an error saying : "ERROR 1060 (42S21): Duplicate column name 'column_name'"

even though i am using the IGNORE, this is not working

i want this to work using the normal SQL statement, instead of a stored procedure

Gordon Linoff · Accepted Answer · 2014-03-04 14:01:02Z

4

According to the documentation:

IGNORE is a MySQL extension to standard SQL. It controls how ALTER TABLE works if there are duplicates on unique keys in the new table or if warnings occur when strict mode is enabled. If IGNORE is not specified, the copy is aborted and rolled back if duplicate-key errors occur. If IGNORE is specified, only one row is used of rows with duplicates on a unique key. The other conflicting rows are deleted. Incorrect values are truncated to the closest matching acceptable value.

That is, it is used for a different purpose, not all errors. What you want is something like ALTER TABLE ADD IF NOT EXISTS, and that syntax doesn't exist.

In a stored procedure, you can use an if statement to see if the column already exists (using INFORMATION_SCHEMA.COLUMNS).

Here is a SQL Fiddle that shows the same problem.

edited Mar 4, 2014 at 14:01

answered Mar 4, 2014 at 13:21

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

Alter ignore table add column if not exists using the SQL statement

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related