CODEWITHSUNDEEP
sql-servertransactionsalter
Bercovici Adrian
Bercovici Adrian

Reputation: 9360

Can not update newly created column

Hello i am altering an SQL table in which i want to add a new column A , and , set its default value based on the value of another column B .How could i do that ?

I have tried so far:

IF NOT EXISTS(SELECT *FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='T' AND COLUMN_NAME='B')
BEGIN
ALTER TABLE T ADD B bit default 0;
UPDATE T
SET B = A

I keep getting the error:

Msg 207, Level 16, State 1, Line 23
Invalid column name 'B'.

P.S : I want to do the two statements atomically ( alter table and update the newly created column)

I was expecting this to be a problem with Intellisense in MSSQL but to be able to run commands in sequential order.

Update

I have also tried to separate into two statements,still to no avail:

IF NOT EXISTS(SELECT *FROM INFORMATION_SCHEMA.COLUMNS 
              WHERE TABLE_NAME='T' AND COLUMN_NAME='B')
ALTER TABLE T ADD B bit default NULL;

UPDATE T
SET B = A
WHERE B = NULL
END

Upvotes: 0

Views: 1383

Answers (2)

AgungCode.Com
AgungCode.Com

Reputation: 787

You need the "GO" command between alter and update.

ALTER TABLE YourTable ADD B bit default 0;
GO
UPDATE YourTable
SET B = A

or

 IF NOT EXISTS(SELECT *FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='T' AND COLUMN_NAME='B')
    BEGIN
      ALTER TABLE T ADD B bit default 0;
      EXEC ('UPDATE product SET B = 1')
END

IF NOT EXISTS(SELECT *FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='T' AND COLUMN_NAME='C')
    BEGIN
      ALTER TABLE T ADD C varchar(20) default null;
      EXEC ('UPDATE product SET C = ''test''')
END

Upvotes: 0

Thom A
Thom A

Reputation: 95574

Before a batch is run, it is parsed by the data engine and any syntax errors and invalid object references are raised. Unfortunately, some DDL statements aren't counted for latter statements. Firstly, if we CREATE and try to INSERT into a table, this works fine:

CREATE TABLE dbo.YourTable (A bit);

INSERT INTO dbo.YourTable (A)
VALUES(1),(0),(1),(1),(0);

On the other hand, if we then try to (in a separate batch) ALTER the table and then UPDATE the value of said column, the batch will fail (as in your example):

ALTER TABLE YourTable ADD B bit default 0;

UPDATE YourTable
SET B = A

One method is to separate the statements into separate batches, however, you won't be able to wrap both statements in your IF then. Therefore, in this scenario, it would be better to have the statement be parsed in the batch later, by executing it with sys.sp_executesql:

IF NOT EXISTS(SELECT *FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='T' AND COLUMN_NAME='B')
BEGIN
    ALTER TABLE T ADD B bit default 0;
    EXEC sys.sp_executesql N'UPDATE T SET B = A;';
END

Upvotes: 3

Related Questions