Link to home
Create AccountLog in
Avatar of peterjim11
peterjim11

asked on

How to drop a column only if it exist?

Question:

I want to drop the column_a in the table_a only if the column_a exist.

What's the sql statement?

ALTER TABLE table_a  DROP COLUMN column_a
Avatar of pollock_d
pollock_d


IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = table_a AND COLUMN_NAME = column_a)
BEGIN
   ALTER TABLE table_a DROP column_a
END

Open in new window


IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = table_a AND COLUMN_NAME = column_a)
BEGIN
   ALTER TABLE table_a DROP COLUMN column_a
END

Open in new window

need quotes around TABLE_NAME = "table_a"....
they seemed to not show up for some reason
single quotes..
Avatar of peterjim11

ASKER

Hi,pollock_d.
When I run your statement in the SQuirrel SQL Client and get the following exception.

Error: java.sql.SQLException: Unexpected token: IF in statement [IF], SQL State: 37000, Error Code: -11
ASKER CERTIFIED SOLUTION
Avatar of pollock_d
pollock_d

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thanks a lot, pollock_d