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

ALTER TABLE [table] DROP COLUMN [column] statement doesnt have a `IF EXISTS` option — Raymond Nijland, Apr 06 '17 at 11:55
http://stackoverflow.com/questions/173814/mysql-alter-syntax-to-drop-a-column-if-it-exists, http://stackoverflow.com/questions/30307935/how-to-drop-column-from-table-if-exists — ADyson, Apr 06 '17 at 11:59
Possible duplicate of [MySQL Alter syntax to drop a column if it exists](https://stackoverflow.com/questions/173814/mysql-alter-syntax-to-drop-a-column-if-it-exists) — Matthias Winkelmann, Aug 28 '17 at 17:19

Pylinux · Answer 1 · 2018-05-29T12:08:42.730

6

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

edited May 29 '18 at 12:08

answered May 29 '18 at 12:00

Pylinux

11,278
4
60
67

Robin · Answer 2 · 2017-04-06T12:00:36.313

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

score 4 · Answer 3 · answered Apr 06 '17 at 12:05

4

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

answered Apr 06 '17 at 12:05

mIhAwk

119
1
7

what can i do ?? – mm gg Apr 06 '17 at 12:08

score 3 · Answer 4 · answered Apr 06 '17 at 12:16

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.

score 1 · Answer 5 · answered Dec 11 '18 at 07:26

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

drop column only if exists in sql

5 Answers5