SQL Alter Table then Modify Values

Question

I have a SQL script that I am working on and I run into an issue when I'm creating (or editing) a column and then attempting to modify that new column.

For example:

BEGIN
    ALTER TABLE SampleTable ADD ColumnThree int 
END

IF (EXISTS (SELECT * FROM sys.columns WHERE name = 'ColumnThree'))
BEGIN
    UPDATE SampleTable SET ColumnThree = 0
END

Now I thought the BEGIN/END blocks would separate those two items out, but I get an error "Invalid column name 'ColumnThree'." when I attempt to run this. Why? Shouldn't the first BEGIN/END set up that ColumnThree and more to the point the IF(EXISTS should protect the UPDATE statement from being run if that column name doesn't exist.

What is the correct way to do something like this? (I seem to have a couple of similar scenarios where this is needed).

Answer 1

You need GO, not BEGIN/END. Also, you may want to edit your EXISTS query a bit to ensure you're getting the right table:

ALTER TABLE SampleTable ADD ColumnThree int 
GO

IF (EXISTS 
        (SELECT 1 
         FROM 
             sys.columns c 
             INNER JOIN sys.tables t ON 
                 c.object_id = t.object_id 
         WHERE 
             t.name = 'SampleTable' 
             AND c.name = 'ColumnThree'))
BEGIN
    UPDATE SampleTable SET ColumnThree = 0
END

If you're using multiple schemas, you will want to through sys.schemas into the check, as well.