CODEWITHSUNDEEP
mysqlsqlmysql-error-1060
Syed Abdul Qadeer
Syed Abdul Qadeer

Reputation: 465

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

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

Upvotes: 5

Views: 9499

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270291

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.

Upvotes: 4

Related Questions