Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
lsulindy
Starting Member
9 Posts |
 Posted - 2009-09-22 : 15:53:58
|
| I have a SQL script:IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMS WHERE TABLE_NAME = 'abc' AND COLUMN_NAME = 'xyz')BEGINALTER TABLE abc ADD xyz BITENDGOUPDATE abc SET xyz = '1' WHERE id in (1, 5, 7)GOI want to UPDATE within the IF NOT EXISTS so that it's only updated once when the column doesn't exist. If I put the update within the if statement I get the error "invalid column name 'xyz'" b/c the column hasn't actually been created yet. It seems like you would have to add a "GO", but you can't b/c that would break the if statement.I read that I could wrap the update in an EXEC b/c "EXEC isn't parsed and compiled until the EXEC statement is reached during the execution of the batch, by which time the column referenced in the statement has already been added to the table." Makes sense and I tried, but I get "Incorrect syntax near the keyword 'UPDATE'":IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMS WHERE TABLE_NAME = 'abc' AND COLUMN_NAME = 'xyz')BEGINALTER TABLE abc ADD xyz BITEXEC (UPDATE abc SET xyz = '1' WHERE id in (1, 5, 7))ENDGOThanks for any help. I'm trying to learn. |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
SQL script to insert and update column