update a column where the column name is a varible

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

SQL Server Development (2000)

update a column where the column name is a varible

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-11-23 : 08:36:54

Dan writes "hi
I have a table with columns 1-12 one for each month.
what I need to do is update the correct column agording to the the current month. I have tried using month(getdate())to call the column name and that did not work.
I also changed the name of the column to Nov and tried the code below and still no success.

declare @monthcol SMALlDATETIME
set @monthcol = convert(char(3),getdate())
update new315_itv
set @monthcol = new315_itv.eventdt
from new315_itv
where new315_itv.eventdt >= '09/01/2004'

I know it can be done I am sure how to go about doing it

Thanks Dan"

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-23 : 08:49:23

The correct answer is to normalise your design then you won't have this problem.
Other than that

declare @sql varchar(1000)
select @sql =
'update new315_itv
set ' + @monthcol + ' = new315_itv.eventdt
from new315_itv
where new315_itv.eventdt >= ''09/01/2004'''
exec (@sql)

==========================================
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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources