drop column only if exists in sql

Question

guys i am totally newbie .. what i want is to delete a column in table only if it exists ... any help here is what i know ALTER TABLE MEN DROP COLUMN Lname but how to make if it exists only in sql ??

Answer 1

Fixed the syntax error in mIhAwk's answer:

if (exists (SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'mytable' AND COLUMN_NAME = 'mycolumn')) 
begin
    ALTER TABLE mytable DROP COLUMN mycolumn
end

Answer 2

What you want to do is not possible in pure MySQL-syntax. However, if you are using a API or something as back-end in a application. You can do the following.

IF (SELECT COUNT(*) 
          FROM table_name.columns 
          WHERE 
          TABLE_SCHEMA = 'database_name' 
          AND TABLE_NAME = 'table_name' 
          AND COLUMN_NAME = 'column_name'>0)
BEGIN
 ALTER TABLE table_name DROP COLUMN column_name
END

Answer 3

Below code solve your problem.

if (exists (SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'mytable' AND COLUMN_NAME = 'mycolumn')) 
begin
     ALTER TABLE 'mytable' DROP COLUMN 'mycolumn'
end