DotnetSparrow DotnetSparrow - 1 month ago 6
SQL Question

SQL script doesnt work but individual queries work

I am using the following script to add a column to my table. When I hit F5 in the query window it gives an error on the 2nd update query that

fkRefTabNo
doesn't exist.

ALTER TABLE EPFieldSQLScripts
ADD fkRefTabNo int DEFAULT 1

update EPFieldSQLScripts
set fkRefTabNo = 1
where fkRefTabNo is null

ALTER TABLE EPFieldSQLScripts
ALTER COLUMN fkRefTabNo INTEGER NOT NULL


But when I run these queries one by one, it doesn't give an error. Can anyone tell me what is wrong with this script?

Answer

Put GO between statements. This way the result of each statement will be committed before running the next one (each statement will be a separate transaction).

ALTER TABLE EPFieldSQLScripts
ADD fkRefTabNo int  DEFAULT 1

GO

update EPFieldSQLScripts
set fkRefTabNo = 1
where fkRefTabNo is null

GO

ALTER TABLE EPFieldSQLScripts
ALTER COLUMN fkRefTabNo INTEGER NOT NULL