CODEWITHSUNDEEP
mysqlsql
mm gg
mm gg

Reputation: 109

drop column only if exists in sql

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 ??

Upvotes: 8

Views: 34231

Answers (5)

Tushar Walzade
Tushar Walzade

Reputation: 3809

IF EXISTS itself is a solution! The following worked for me for both MySQL & SqlServer -

ALTER TABLE #TempTable DROP COLUMN IF EXISTS [ClientId], [VendorId], [UserId]

It works like a charm for multiple columns too!

Tested it on - mysql Ver 15.1 Distrib 10.1.22-MariaDB, for Win32 (AMD64) & SSMS version 14.0.17254.0

Upvotes: 1

Pylinux
Pylinux

Reputation: 11816

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

Upvotes: 6

Ike Walker
Ike Walker

Reputation: 65547

As mentioned by others, MySQL does not support DROP COLUMN IF EXISTS, so your best approach is simply to run DROP COLUMN and ignore the error if the column doesn't exist.

A while back I wrote about an approach to simulate DROP COLUMN IF EXISTS using common_schema. That may work for you. Check out the relevant blog post for more details.

Upvotes: 3

mIhAwk
mIhAwk

Reputation: 117

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

Upvotes: 4

Robin
Robin

Reputation: 76

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

Upvotes: 4

Related Questions