how to drop a column if exists in mysql?

mysql sql alter
CloudSeph picture CloudSeph · Sep 28, 2016 · Viewed 23.9k times · Source 
ALTER TABLE user DROP COLUMN registered_date

We can drop a column using the above command. But how can i drop it only if the column registered_date exist?

Answer

Jibin Balachandran picture Jibin Balachandran · Sep 28, 2016 
IF EXISTS(SELECT * 
          FROM information_schema.COLUMNS 
          WHERE 
          TABLE_SCHEMA = 'db_name' 
          AND TABLE_NAME = 'table_name' 
          AND COLUMN_NAME = 'column_name')
BEGIN
 ALTER TABLE table_name DROP COLUMN column_name
END

OR

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

Related questions

How to move columns in a MySQL table?

Currently I am having the following MySQL table: Employees (empID, empName, department); I want to change the table to the following: Employees (empID, department, empName); How can this be done using ALTER statements? Note: I want to change only column …

Read More
Alter SQL table - allow NULL column value

Initially, the table "MyTable" has been defined in the following way: CREATE TABLE IF NOT EXISTS `MyTable` ( `Col1` smallint(6) NOT NULL AUTO_INCREMENT, `Col2` smallint(6) DEFAULT NULL, `Col3` varchar(20) NOT NULL, ); How to update it in such a way that …

Read More
How to add a column to a table from another table in Mysql?

I have two tables table1 table2 Tabel1 contains 2 columns id Name Tabel2 contains 2 columns id Age A want to add age column from table2 to table1 (WHERE table1.id = table2.id) Then table1 should contains 3 columns id Name Age

Read More