drop column if exists - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

drop column if exists

Author

Topic

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2009-02-04 : 20:12:51

Hi guys
i've to drop column if exists. it worked first fine but if i run 2nd time onwards on same database i get "Invalid column name 'FK_ORG_EbatchID'." error. have not followed whats happening here

my script
IF EXISTS (select * from sys.columns where Name = N'FK_ORG_EbatchID'
and Object_ID = Object_ID(N'r_msgtype'))
BEGIN
UPDATE r_msgtype SET fk_finEbatch_ebatchid = FK_ORG_EbatchID WHERE FK_ORG_EbatchID IS NOT NULL

ALTER TABLE [dbo].[r_msgtype] DROP CONSTRAINT fk_msgtype_orgebatchID
ALTER TABLE [dbo].[r_msgtype] DROP COLUMN FK_ORG_EbatchID
END

Cheers

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2009-02-04 : 20:14:02

even though column no longer exists it still goes inside IF !!!

Cheers

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-04 : 20:23:52

Do you mean this?

IF EXISTS (select * from sys.columns where Name = N'FK_ORG_EbatchID' and Object_ID = Object_ID(N'r_msgtype'))

BEGIN
UPDATE [r_msgtype] 
SET [fk_finEbatch_ebatchid] = [FK_ORG_EbatchID]
WHERE FK_ORG_EbatchID IS NOT NULL 

End 
Else
Begin
ALTER TABLE [dbo].[r_msgtype] Add [FK_ORG_EbatchID] int
END

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2009-02-04 : 20:27:13

i never meant to add it again. if its there just transfer data to new field and drop it. sometimes our script may run multiple times to avoid any error i wrapped around IF exists statement but some reason it still goes inside the IF even though field does not exist any more .

Cheers

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-04 : 20:32:38

quote:
Originally posted by rajani

i never meant to add it again. if its there just transfer data to new field and drop it. sometimes our script may run multiple times to avoid any error i wrapped around IF exists statement but some reason it still goes inside the IF even though field does not exist any more .

Cheers

Your logic doesn't make sense.

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2009-02-04 : 20:35:18

let me explain again. we've a column that we want to drop but before dropping if it has any data transfer it to another field and drop it. how would u do it ?

Cheers

saumilt
Starting Member

1 Post

Posted - 2009-07-31 : 11:14:25

Were you able to find solution to this issue.

Please let me know if you found solution to this problem.

Please tell me how did you resolve this issue

Sam Thakkar
DBA
Tacito Direct Marketing

rocknpop
Posting Yak Master

201 Posts

Posted - 2009-07-31 : 11:51:49

What is happening here is that the DML statement is first checked for appropriate columns, if not found it simply throws an error. Since the first run has already deleted the column the second run will not succeed as it is not a valid update statement. It is the same as not able to fire an update/insert/delete if a column is missing in the table. Thus in the second run the flow is not going into the IF, you can test this by either commenting out the update and printing some arbitrary value.

Is it mandatory for you to drop the column. Instead of dropping can you make the column NULL after the update. Or if you need to drop make the Update as part of a dynamic Sql.

--------------------
Rock n Roll with SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources