Invalid column name error

Question

Post reply

Invalid column name error

rwsIII

Old Hand

Points: 359
More actions
February 3, 2006 at 4:37 pm

#152731

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Rodrigo Acosta SSC-Insane Points: 21261 More actions · Answer 1

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)

rwsIII Old Hand Points: 359 More actions · Answer 2

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!

Liliana Sorrentino SSC-Addicted Points: 400 More actions · Answer 3

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.

rwsIII Old Hand Points: 359 More actions · Answer 4

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

DSP-120308 Hall of Fame Points: 3959 More actions · Answer 5

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