February 3, 2006 at 4:37 pm
Hey all-
I am trying to add a column to a table, and then update that newly added column. these two things are relatively simple, however when I place them inside an 'if' statement, i get the error:
Invalid column name 'TOTAL_VOTES'
Here is my SQL:
if (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'RESULTS'
AND COLUMN_NAME = 'TOTAL_VOTES'
) = 0
begin
ALTER TABLE [dbo].[RESULTS] ADD [TOTAL_VOTES] int not NULL DEFAULT (0); --WITH VALUES
UPDATE RESULTS SET TOTAL_VOTES = (OPTION1 + OPTION2 + OPTION3 + OPTION4 + OPTION5 + OPTION6 + OPTION7 + OPTION8 + OPTION9 + OPTION10)
end
i need the if check b/c I only want to add and populate the column if it doesn't already exist in the table. (prior to this I check to see if the table exists)
I have tried to set a variable and then based off of that variable, populate the new column, but that doesn't work either.
any suggestions? Why does the if() begin/end seem to cause errors with the alter/update statements?
How can I get around this?
Thanks
February 3, 2006 at 8:51 pm
Are you sure you are referencing the same table with the same owner?
Try updating the table specifying the owner
UPDATE dbo.RESULTS SET TOTAL_VOTES = (OPTION1 + OPTION2 + OPTION3 + OPTION4 + OPTION5 + OPTION6 + OPTION7 + OPTION8 + OPTION9 + OPTION10)
February 7, 2006 at 8:40 am
Hey Rodrigo-
I am referencing the same table, I changed my t-sql statement and received the same error:
Invalid column name 'TOTAL_VOTES'
any other ideas?
thanks!
February 7, 2006 at 9:10 am
Hello,
Try with GO before UPDATE:
IF(SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'mytable'
AND COLUMN_NAME = 'mycol'
) = 0
ALTER TABLE mytable ADD mycol int not NULL DEFAULT (0)
GO
UPDATE mytable SET mycol = myval
Liliana.
February 7, 2006 at 9:54 am
Hey Liliana-
Thanks for suggestion. I tried that, and T-SQL complains about syntax. I think that 'GO' basicly executes all the script prior to it. So because I have the Alter table, and Update statements inside of 'begin/end', the syntax gets funny in that the 'begin' has no 'end'. I need the 'begin/end' b/c if the column does not exist on the table, i don't want to issue the update statement.
The solution I have come up with is the following:
if ( SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'RESULTS'
AND COLUMN_NAME = 'TOTAL_VOTES'
) = 0
BEGIN
exec('ALTER TABLE [dbo].[RESULTS] ADD [TOTAL_VOTES] int not NULL DEFAULT (0);');
exec('UPDATE RESULTS SET TOTAL_VOTES = (OPTION1 + OPTION2 + OPTION3 + OPTION4 + OPTION5 + OPTION6 + OPTION7 + OPTION8 + OPTION9 + OPTION10);');
end
I am bascily forcing the T-SQL to execute by using the exec() command.
Thanks for everyone's help!
Leme know if you have a better way to solve this!
Thanks
February 7, 2006 at 3:26 pm
Try something like this:
if exists(SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'RESULTS'
AND COLUMN_NAME = 'TOTAL_VOTES')
BEGIN
SELECT 0
END
ELSE
BEGIN
exec('ALTER TABLE [dbo].[RESULTS] ADD [TOTAL_VOTES] int not NULL DEFAULT (0);')
exec('UPDATE RESULTS SET TOTAL_VOTES = (OPTION1 + OPTION2 + OPTION3 + OPTION4 + OPTION5 + OPTION6 + OPTION7 + OPTION8 + OPTION9 + OPTION10);');
END
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy