x

Alter table invalid column

I had a alter table that used to work and now its not and I dont know why when I run the below I get Invalid column name 'Q'. Many thanks if anyone can work this out

Alter Table [all] Add [Q] varchar(1)

Update dbo.[all] Set Q = '1' Where [IncpDt] between '2010-01-01' and '2010-03-31'

more ▼

asked May 24, 2012 at 01:42 PM in Default

n3w2sql gravatar image

n3w2sql
850 14 21 33

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Add the batch separator GO to the code

Alter Table [all] Add [Q] varchar(1)
GO
Update dbo.[all] Set Q = '1' Where [IncpDt] between '2010-01-01' and '2010-03-31'
more ▼

answered May 24, 2012 at 01:45 PM

Kev Riley gravatar image

Kev Riley ♦♦
52.7k 47 49 76

Now I feel silly "so simple" when you know how Thanks Kev
May 24, 2012 at 01:50 PM n3w2sql
+1 for using your eyes (which I didn't...) and for beating me to the answer.
May 24, 2012 at 01:53 PM Magnus Ahlkvist
@n3w2sql - not at all, it's a common mistake, after all it 'reads' ok. It's just that the parser throws the error for the 2nd statement as it has no idea what 'Q' is, and based on the metadata of the table at runtime, it's an error!
May 24, 2012 at 01:56 PM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

EDIT

I see that Kev answered the question with a GO between the statements. But my first remark still applies

END EDIT

First of all I must point out that you should never use varchar(1) as a data type for a column. varchar means that the column is of variable length. If the field is 0 or 1 character long, you should use the char(1) data type. Using a variable length data type uses up extra space.

Second: It's not clear in your question if it is the ALTER TABLE statement that's causing the error or if it's the UPDATE statement.

If it's the later - the UPDATE statement - , it's probably because you haven't schema qualified the table name in your ALTER TABLE statement, and you might have another default schema than 'dbo'. Check in sys.tables if you have more than one table named 'all'.

If it's the former - ALTER TABLE statement causes the error - I can't really see anything wrong with it, except I think you should make it a habit to always schema qualify your table names, and you should avoid varchar(1).
more ▼

answered May 24, 2012 at 01:52 PM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.4k 16 19 33

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1833

asked: May 24, 2012 at 01:42 PM

Seen: 1089 times

Last Updated: May 24, 2012 at 01:56 PM