SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Invalid column name error


Invalid column name error

Author
Message
rwsIII
rwsIII
SSC Veteran
SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)

Group: General Forum Members
Points: 243 Visits: 2
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
Rodrigo Acosta
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16257 Visits: 24

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
rwsIII
SSC Veteran
SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)

Group: General Forum Members
Points: 243 Visits: 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
Liliana Sorrentino
SSC-Addicted
SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)SSC-Addicted (400 reputation)

Group: General Forum Members
Points: 400 Visits: 29

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
rwsIII
SSC Veteran
SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)SSC Veteran (243 reputation)

Group: General Forum Members
Points: 243 Visits: 2

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
DSP-120308
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3047 Visits: 323

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search