Invalid column name error

• rwsIII

  Old Hand

  Points: 309

  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

• Rodrigo Acosta

  SSC-Insane

  Points: 21261

  More actions

  February 3, 2006 at 8:51 pm

  #618832

  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: 309

  More actions

  February 7, 2006 at 8:40 am

  #619310

  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

  February 7, 2006 at 9:10 am

  #619323

  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: 309

  More actions

  February 7, 2006 at 9:54 am

  #619339

  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: 3899

  More actions

  February 7, 2006 at 3:26 pm

  #619424

  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