How to write update to add column with value for existing

Question

I have a table Members with existing data, I want to add a non-nullable bit column called 'IsOnlineUser', I want all my existing rows to be set to false. I have a set of scripts that run each time I deploy so I need a check to see if the table

The first SQL I tried was

SET @ColumnExists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 

WHERE TABLE_NAME = 'Member' AND COLUMN_NAME = 'IsOnlineUser');

IF (@ColumnExists = 0)
BEGIN
    ALTER TABLE Member ADD IsOnlineUser bit NULL;

    UPDATE Member SET IsOnlineUser= 0;
    ALTER TABLE Member ALTER COLUMN IsOnlineUser bit NOT NULL;
END
GO

But that gives me

Invalid column name 'IsOnlineUser'

. Assumedly this is because the UPDATE fails to find the created column so I thought if I put a 'GO' between the two statements it would help so I did the following:

SET @ColumnExists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 

WHERE TABLE_NAME = 'Member' AND COLUMN_NAME = 'IsOnlineUser');

IF (@ColumnExists = 0)
BEGIN
    ALTER TABLE Member ADD IsOnlineUser bit NULL;

END
GO 

IF (@ColumnExists = 0)
BEGIN
    UPDATE Member SET IsOnlineUser= 0;
    ALTER TABLE Member ALTER COLUMN IsOnlineUser bit NOT NULL;
END
GO

However this says

Must declare the scalar variable "@ColumnExists".

Assumedly this is because of the GO stopping me access the scalar variable between the two.

It seems like a fairly common use case, so I assume I am just missing something, any help would be much appreciated

Wouldn't would you need to set@columnExists = (...) again after the go? https://stackoverflow.com/questions/937336/is-there-a-way-to-persist-a-variable-across-a-go since variables don't persist across go? — xQbert, Aug 16 '17 at 18:19

SqlZim · Accepted Answer · 2017-08-16T18:26:57.330

You could add the column as not null with a default constraint:

alter table Member add IsOnlineUser bit not null default 0;

Optionally, you can give the constraint a specific name at the same time like so:

alter table member 
  add IsOnlineUser bit not null 
    constraint df_Member_IsOnlineUser default 0;

To simplify your if, you skip the variable and use not exists() like so:

if not exists (
  select 1 
  from information_schema.columns 
  where table_name = 'Member' 
    and column_name = 'IsOnlineUser'
  )
begin;
alter table member 
  add IsOnlineUser bit not null 
    constraint df_Member_IsOnlineUser default 0;
end;

If you just want to make the existing code work while maintaining your current logic, you can execute sql strings instead of inline code with exec or sp_executesql.

...
if (@ColumnExists = 0)
begin;
    exec sp_executesql N'alter table Member add IsOnlineUser bit null';
    exec sp_executesql N'update Member set IsOnlineUser= 0;';
    exec sp_executesql N'alter table Member alter column IsOnlineUser bit not null;';
end;
go

score 0 · Answer 2 · answered Aug 16 '17 at 18:56

You can check if column does not exist and add it if needed. Put GO and go for UPDATE and ALTER after that. It will run correctly in both the cases wherever column exist or not. No need for variables.

IF NOT EXISTS (SELECT * FROM sys.columns WHERE name = 'IsOnlineUser' AND object_id = OBJECT_ID('Member'))
ALTER TABLE Member ADD IsOnlineUser bit NULL;
GO
UPDATE Member SET IsOnlineUser= 0;
ALTER TABLE Member ALTER COLUMN IsOnlineUser bit NOT NULL;
GO

Note: I prefer the use of SQL Server's sys views rather than ANSI INFORMATION.SCHEMA, and wanted to show that alternative, but that's just me. You can keep INFORMATION.SCHEMA if you like.

Edit-PS: Variable exist only within the one batch. After you use GO, you can't use variables declared prior to.

How to write update to add column with value for existing

2 Answers2