Add column and update - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Add column and update

Author

Topic

iminore
Posting Yak Master

141 Posts

Posted - 2007-05-04 : 10:58:58

Trying to run this:

if not exists(select O.name from sysobjects O left join syscolumns C on O.id = C.id where O.name = 'tablea' and C.name = 'apparea')
begin
alter table tablea add apparea varchar(8) NULL
update tablea set apparea = 'xxx'
end

I get error:
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'apparea'.

Any ideas?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-04 : 11:01:47

you need a go between the alter and update

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

iminore
Posting Yak Master

141 Posts

Posted - 2007-05-04 : 11:09:30

get these errors then:

Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near 'NULL'.
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'end'.

Surely a 'GO' should never be between a 'begin' and 'end'

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-04 : 11:11:54

exactly my point.
you can't do this the weay you want it to.

try
if not exists(select O.name from sysobjects O left join syscolumns C on O.id = C.id where O.name = 'tablea' and C.name = 'apparea')
begin
exec('alter table tablea add apparea varchar(8) NULL')
update tablea set apparea = 'xxx'
end

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

iminore
Posting Yak Master

141 Posts

Posted - 2007-05-04 : 11:23:17

Same original error.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-04 : 11:38:06

hm... try running the if with out the update and see if the table contains the column.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

iminore
Posting Yak Master

141 Posts

Posted - 2007-05-04 : 11:41:30

It works without the update. The point is if the column is already there I don't want to update the table.
It seems the 'add column' does not complete in some sense before the update runs.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-04 : 11:47:17

[code]
if not exists(select O.name from sysobjects O left join syscolumns C on O.id = C.id where O.name = 'tablea' and C.name = 'apparea')
begin
alter table tablea add apparea varchar(8) NULL
end
go
update tablea set apparea = 'xxx' where apparea is null
[/code]

iminore
Posting Yak Master

141 Posts

Posted - 2007-05-04 : 12:10:18

Think you are missing the point.

I DON'T want to update the table if the column is already there.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-04 : 12:27:48

if the column is already there, the value will not be NULL as you would have update it when you first added the column

nr
SQLTeam MVY

12543 Posts

Posted - 2007-05-04 : 13:13:02

You can't change a structure and access it in the same batch.

if not exists(select O.name from sysobjects O left join syscolumns C on O.id = C.id where O.name = 'tablea' and C.name = 'apparea')
begin
exec('alter table tablea add apparea varchar(8) NULL')
exec('update tablea set apparea = ''xxx''')
end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

iminore
Posting Yak Master

141 Posts

Posted - 2007-05-08 : 03:43:00

Khtan - I don't see why the value will not be null if already there.
nr - spot on. What you say makes sense and it works. many thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources