UPDATE to new column creates 'Invalid column name' error

Question

UPDATE to new column creates 'Invalid column name' error

JustANumber

SSC Eights!

Points: 905
More actions
April 1, 2010 at 10:08 am

#386623

I have a script to insert a new NOT NULL column into a table and give it a default value. Within the same data script I want to modify several of the default values to other values. The UPDATE statement below results in a "Invalid column name 'NewColumn'" error.
UPDATE dbo.[Table] SET NewColumn = 'ValueB' WHERE Acronym = 'ValueA'
If I run the script without the UPDATE the new field is inserted. Adding the UPDATE statement causes SQL Server to think it can't update a field that hasn't been created yet.
How can I force SQL Server to insert the new column then recognize the eventual update statement within the same script?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Stick a GO between the ALTER TABLE and the update. Otherwise, at parse-time, the column doesn't exist and throws an error.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

JustANumber SSC Eights! Points: 905 More actions · Answer 2

Unfortunately, my column insert is not performed with an ALTER statement. I first create a temp table where the new column is 5th out of 8 columns. 2)Insert original table values into the temp table 3) Drop the old table 4) Rename the temp table, and 5) Attempt to UPDATE values in my new column.

And inserting a GO statement after my CREATE TABLE statement still results in a Invalid column name error in the UPDATE statement.

Paul White SSC Guru Points: 150467 More actions · Answer 3

JustANumber (4/1/2010)
Unfortunately, my column insert is not performed with an ALTER statement

Can I ask why not? Are you relying on 'column order' for something?

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

JustANumber (4/1/2010)
Unfortunately, my column insert is not performed with an ALTER statement. I first create a temp table where the new column is 5th out of 8 columns. 2)Insert original table values into the temp table 3) Drop the old table 4) Rename the temp table, and 5) Attempt to UPDATE values in my new column.

Why that convoluted route?

Post code?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

JustANumber SSC Eights! Points: 905 More actions · Answer 5

All our tables of this nature have ModifiedOn, ModifiedBy, and RowCheckSum fields as the last three fields in the table. So the new field had to be inserted before them.

A bit of a hassle, but that's what I'm working with.

Lowell SSC Guru Points: 323518 More actions · Answer 6

when you add the column with the default, you could include the WITH VALUES statment so that existing data gets the new default; then you would not have to update seperately (if the value is supposed to be the default)

ALTER TABLE TBCOUNTY ADD CREATEDDATE DATETIME DEFAULT GETDATE() WITH VALUES

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

JustANumber SSC Eights! Points: 905 More actions · Answer 7

17 of the 23 rows in the table get the default value. The other 6 rows get updated with 6 distinct values.

Here's the relevant parts of the script. Names have been changed to protect me.

IF NOT EXISTS ( SELECT 1 FROM sys.columns WHERE [object_id] = OBJECT_ID('dbo.Service') AND [name] = 'NewColumn')

BEGIN

CREATE TABLE tmp_ms_xx_Service

(Column1 uniqueidentifier NOT NULL,

Column2 char(10) NULL,

Column3 varchar(100) NOT NULL,

Column4 char(1) NOT NULL,

NewColumn varchar(15) NOT NULL,

Column6 datetime NOT NULL,

Column7 varchar(50) NOT NULL,

Column8 checksum NOT NULL

) ON [DATA]

GO

INSERT tmp_ms_xx_Service

(Column1 , Column2 , Column3 , Column4 ,

NewColumn , Column6 , Column7)

SELECT Column1 , Column2 , Column3 , Column4 ,

'DefaultValue' , Column6 , Column7

FROM dbo.[Service]

DROP TABLE dbo.[Service]

EXEC sp_rename N'[dbo].[tmp_ms_xx_Service]', N'Service'

UPDATE dbo.[Service] SET NewColumn = 'Val1' WHERE Column2 = 'Val2'

UPDATE dbo.[Service] SET NewColumn = 'Val3' WHERE Column2 = 'Val4'

UPDATE dbo.[Service] SET NewColumn = 'Val5' WHERE Column2 = 'Val6'

UPDATE dbo.[Service] SET NewColumn = 'Val7' WHERE Column2 = 'Val8'

UPDATE dbo.[Service] SET NewColumn = 'Val9' WHERE Column2 = 'Val10'

UPDATE dbo.[Service] SET NewColumn = 'Val11' WHERE Column2 = 'Val12'

END

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Try:

IF NOT EXISTS ( SELECT 1 FROM sys.columns WHERE [object_id] = OBJECT_ID('dbo.Service') AND [name] = 'NewColumn')

BEGIN

CREATE TABLE tmp_ms_xx_Service

(Column1 uniqueidentifier NOT NULL,

Column2 char(10) NULL,

Column3 varchar(100) NOT NULL,

Column4 char(1) NOT NULL,

NewColumn varchar(15) NOT NULL,

Column6 datetime NOT NULL,

Column7 varchar(50) NOT NULL,

Column8 checksum NOT NULL

) ON [DATA]

GO

INSERT tmp_ms_xx_Service

(Column1 , Column2 , Column3 , Column4 ,

NewColumn , Column6 , Column7)

SELECT Column1 , Column2 , Column3 , Column4 ,

'DefaultValue' , Column6 , Column7

FROM dbo.[Service]

DROP TABLE dbo.[Service]

EXEC sp_rename N'[dbo].[tmp_ms_xx_Service]', N'Service'

END

GO

UPDATE dbo.[Service] SET NewColumn = 'Val1' WHERE Column2 = 'Val2'

UPDATE dbo.[Service] SET NewColumn = 'Val3' WHERE Column2 = 'Val4'

UPDATE dbo.[Service] SET NewColumn = 'Val5' WHERE Column2 = 'Val6'

UPDATE dbo.[Service] SET NewColumn = 'Val7' WHERE Column2 = 'Val8'

UPDATE dbo.[Service] SET NewColumn = 'Val9' WHERE Column2 = 'Val10'

UPDATE dbo.[Service] SET NewColumn = 'Val11' WHERE Column2 = 'Val12'

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

JustANumber SSC Eights! Points: 905 More actions · Answer 9

That script modification solves the problem. Thank you.