CODEWITHSUNDEEP
sql-server
user2145482
user2145482

Reputation: 27

Column not found

I tried below in sql server management, in a single query.

alter table add column amount2

update table set amount2=amount

I am getting column amount2 not found.

Can anyone tell me why this error?

Upvotes: 1

Views: 2686

Answers (3)

bezmachine
bezmachine

Reputation: 151

You can get around this problem like this:

-- Alter the table and add new column "NewColumn"

ALTER TABLE [MyTable] ADD [NewColumn] CHAR(1) NULL;

-- Set the value of NewColumn

EXEC ('UPDATE [MyTable] SET [NewColumn] = ''A'' ');

Upvotes: 1

jdl
jdl

Reputation: 1104

There's a couple things wrong here.

  1. The correct syntax for adding a column is MSDN - ALTER TABLE

    ALTER TABLE [TableName] ADD [ColumnNAME] [DataType]

  2. 'Table' is a Reserved Keyword in SQL Server, although it is possible to have a table named 'Table'. You need to include brackets when referencing it.

    SELECT * FROM [Table]

All together, you need

ALTER TABLE [Table] ADD [Amount2] INT
GO -- See Martin's answer for reason why 'GO' is needed here

UPDATE [Table] SET [Amount2]  = [Amount]

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453298

That is not valid syntax (misses table name and column datatype) but in management studio use the batch separator GO between adding a column to an existing table and statements referencing the new column anyway.

Or alternatively you can use EXEC to execute it in a child batch.

SQL Server tries to compile all statements in the batch before execution and this will fail when it encounters the statement using this column.

Upvotes: 5

Related Questions