CODEWITHSUNDEEP
sqlalter-table
ChrisHDog
ChrisHDog

Reputation: 4663

SQL Alter Table then Modify Values

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).

Upvotes: 3

Views: 7811

Answers (1)

Eric
Eric

Reputation: 95203

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.

Upvotes: 6

Related Questions