CODEWITHSUNDEEP
sqlsql-server
Craig
Craig

Reputation: 18734

Update a Column that has been added in the same script

I have a deployment script that needs to add a column, and then populate it with some data. I check if the column exists - if it doesn't I add it, and attempt to change the value.

    IF NOT EXISTS (SELECT * FROM   sys.columns WHERE  object_id = OBJECT_ID(N'[dbo].[MyTable]') AND name = 'MyColumn')
    BEGIN

        ALTER TABLE [dbo].MyTable
        ADD MyColumn INT NULL

...

       UPDATE MyTable SET MyColumn = MyValue

END

However, the script fails (on pre-compile?) as it says that MyColumn doesn't exist.

The only way I can think of fixing this, is to change the UPDATE statement to dynamic SQL, and EXEC it that way.

Is there a better way to do this?

Upvotes: 1

Views: 34

Answers (2)

Hardik Sheth
Hardik Sheth

Reputation: 47

you should put your update statement out side of the IF NOT EXISTS condition.

Reason : If you have column already present in your table, then it will exit the condition and execute the update statement, else it will add the column and then perform the update. have a look at below code:

IF NOT EXISTS (SELECT * FROM   sys.columns WHERE  object_id = 
OBJECT_ID(N'[dbo].[MyTable]') AND name = 'MyColumn')
BEGIN

    ALTER TABLE [dbo].MyTable
    ADD MyColumn INT NULL

END

GO

UPDATE MyTable SET MyColumn = 1
GO

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270873

This is tricky because of the compilation. One solution is dynamic SQL:

exec sp_executesql 'UPDATE MyTable SET MyColumn = MyValue';

If you take this path, then you should pass in the value as a parameter.

Upvotes: 1

Related Questions