Invalid column name error

  • rwsIII

    Old Hand

    Points: 309

    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

    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

    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

    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

    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

    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