Dynamic update of a column

Hi,
In the code below, I am trying to update a column to '0'. But, I dont know
which column to update until my variable @. Month fetches the column name.
DECLARE @.Month Varchar(20)
SELECT @.Month = MD.sFieldName FROM MonthDefinition MD, DTSScheduler DTS
WHERE MD.lMonthId = DTS.JobMonth
and DTS.JobId = 2
PRINT @.Month
update FTEActualForecast set @.Month = 0
where FTEActualForecast.lRCId in (select FAF.lRCId from FTEActualForecast
FAF, FTEPayrollSource FPS
where FAF.lRCId = FPS.RCCode and FAF.lChartId = FPS.ChartCode)
and FTEActualForecast.lChartId in (select FAF.lChartId from
FTEActualForecast FAF, FTEPayrollSource FPS
where FAF.lRCId = FPS.RCCode and FAF.lChartId = FPS.ChartCode)
Now, the problem is
1. The column I am trying to update to '0'is determined dynamically, so I
have used a variable name called @.Month
2. But in the UPDATE statement when I use the keyword SET it sets the
variable and not the column value to ‘zero’
Is there some workaround or better way of doing this ?
Please advise.
Cheers,
Hemil.Try
Exec ('update FTEActualForecast set ' + @.Month + '= 0
where FTEActualForecast.lRCId in (select FAF.lRCId from FTEActualForecast
FAF, FTEPayrollSource FPS
where FAF.lRCId = FPS.RCCode and FAF.lChartId = FPS.ChartCode)
and FTEActualForecast.lChartId in (select FAF.lChartId from
FTEActualForecast FAF, FTEPayrollSource FPS
where FAF.lRCId = FPS.RCCode and FAF.lChartId = FPS.ChartCode)')
"Hemil" wrote:

> Hi,
> In the code below, I am trying to update a column to '0'. But, I dont kno
w
> which column to update until my variable @. Month fetches the column name.
> DECLARE @.Month Varchar(20)
> SELECT @.Month = MD.sFieldName FROM MonthDefinition MD, DTSScheduler DTS
> WHERE MD.lMonthId = DTS.JobMonth
> and DTS.JobId = 2
> PRINT @.Month
> update FTEActualForecast set @.Month = 0
> where FTEActualForecast.lRCId in (select FAF.lRCId from FTEActualForecast
> FAF, FTEPayrollSource FPS
> where FAF.lRCId = FPS.RCCode and FAF.lChartId = FPS.ChartCode)
> and FTEActualForecast.lChartId in (select FAF.lChartId from
> FTEActualForecast FAF, FTEPayrollSource FPS
> where FAF.lRCId = FPS.RCCode and FAF.lChartId = FPS.ChartCode)
> Now, the problem is
> 1. The column I am trying to update to '0'is determined dynamically, so I
> have used a variable name called @.Month
> 2. But in the UPDATE statement when I use the keyword SET it sets the
> variable and not the column value to ‘zero’
>
> Is there some workaround or better way of doing this ?
> Please advise.
> Cheers,
> Hemil.
>|||Thanks a lot, Tom.
It does the job.
Thanks again,
Hemil.
"Tom" wrote:
> Try
> Exec ('update FTEActualForecast set ' + @.Month + '= 0
> where FTEActualForecast.lRCId in (select FAF.lRCId from FTEActualForecast
> FAF, FTEPayrollSource FPS
> where FAF.lRCId = FPS.RCCode and FAF.lChartId = FPS.ChartCode)
> and FTEActualForecast.lChartId in (select FAF.lChartId from
> FTEActualForecast FAF, FTEPayrollSource FPS
> where FAF.lRCId = FPS.RCCode and FAF.lChartId = FPS.ChartCode)')
>
> "Hemil" wrote:
>|||Please include DDL with questions like this so that we don't have to guess
what your table looks like.
Why separate columns for each month? Instead, add a DATETIME column to
record the month then you can specify which row to update with a WHERE
clause.
David Portas
SQL Server MVP
--