1

In one SQL script (SQL Server 2016) I want to add a column by using ALTER TABLE and change the values by using UPDATE:

IF NOT EXISTS (SELECT * FROM sys.all_columns  
               WHERE object_id = OBJECT_ID(N'MyTable') and NAME = 'MyAttribute')
BEGIN
    ALTER TABLE MyTable 
        ADD MyAttribute BIT NOT NULL DEFAULT(0);

    UPDATE MyTable 
    SET MyAttribute = 1;
END

but I get an error

Invalid column name 'MyAttribute'

for line 5 (the update statement).

The background for the code is:

  • The script is running every time a new program version for the database is installed (I can't change this behaviour)
  • MyAttribute should only be added at the first time of script execution
  • The value for all existing records should be 1, but the default value for all new records must be 0
Dale K
  • 25,246
  • 15
  • 42
  • 71
Juergen2018
  • 33
  • 4

1 Answers1

0

They need to run in different batches:

declare @new bit = 0;

IF NOT EXISTS (SELECT * FROM sys.all_columns  
               WHERE object_id = OBJECT_ID(N'MyTable') and NAME = 'MyAttribute')
BEGIN
    set @new = 1;
    ALTER TABLE MyTable ADD MyAttribute BIT NOT NULL DEFAULT(0);

END
Go
if @new = 1
begin
    UPDATE MyTable SET MyAttribute = 1;
end

EDIT:

IF NOT EXISTS (SELECT * FROM sys.all_columns  
               WHERE object_id = OBJECT_ID(N'MyTable') and NAME = 'MyAttribute')
BEGIN
    ALTER TABLE MyTable 
        ADD MyAttribute BIT NOT NULL 
        CONSTRAINT df_attr DEFAULT(1);
    ALTER TABLE MyTable
        DROP CONSTRAINT df_attr; 
    ALTER TABLE MyTable
        ADD CONSTRAINT df_attr DEFAULT 0 FOR MyAttribute; 
END
Cetin Basoz
  • 22,495
  • 3
  • 31
  • 39
  • This version with the "@new" -Flag unfortunately doesn't work, because the variable "new" isn't valid after the "Go". I've got the error: Must declare the scalar variable "@new". – Juergen2018 Mar 31 '20 at 09:40
  • @Juergen2018, you are right. Then my first reaction was to add it as an EXEC sp_executesql inside the block, but that doesn't work either. I came up with the solution in the edited version, which does work. – Cetin Basoz Mar 31 '20 at 12:08
  • your edited version works fine, thanks a lot for the solution!! – Juergen2018 Mar 31 '20 at 14:41