Update Query, SET with nested IIF

Need help with syntax on updating a joined table that has columns for months, need to update the months that are Month(DATE()) only in SET on a inner join table.

Update dbo_MasterSalesForecast_Test

INNER JOIN MasterSalesForecastUpdate

ON (dbo_MasterSalesForecast_Test.SubTo=MasterSalesForecastUpdate.Item) AND( dbo_MasterSalesForecast_Test.Planner=MasterSalesForecastUpdate.Planner)

IIF(Month(Date()) =”1″,( SET

dbo_MasterSalesForecast_Test.[YY:01] = [MasterSalesForecastUpdate].[YYJan],

dbo_MasterSalesForecast_Test.[YY:02] = [MasterSalesForecastUpdate].[YYFan],

dbo_MasterSalesForecast_Test.[YY:03] = [MasterSalesForecastUpdate].[YYMar])

,

IIF(Month(Date())=”2″,( SET

dbo_MasterSalesForecast_Test.[YY:02]=[MasterSalesForecastUpdate].[YYFeb],

dbo_MasterSalesForecast_Test.[YY:03]=[MasterSalesForecastUpdate].[YYMar],

dbo_MasterSalesForecast_Test.[YY:04]=[MasterSalesForecastUpdate].[YYApr])

,

IIF(Month(Date())=”3″,( SET

dbo_MasterSalesForecast_Test.[YY:03]=[MasterSalesForecastUpdate].[YYMar],

dbo_MasterSalesForecast_Test.[YY:04]=[MasterSalesForecastUpdate].[YYApr],

dbo_MasterSalesForecast_Test.[YY:05]=[MasterSalesForecastUpdate].[YYMay])

,

IIF(Month(Date())=”4″,(SET

dbo_MasterSalesForecast_Test.[YY:04]=[MasterSalesForecastUpdate].[YYApr],

dbo_MasterSalesForecast_Test.[YY:05]=[MasterSalesForecastUpdate].[YYMay],

dbo_MasterSalesForecast_Test.[YY:06]=[MasterSalesForecastUpdate].[YYJun])

,

IIF(Month(Date())=”5″,(SET

dbo_MasterSalesForecast_Test.[YY:05]=[MasterSalesForecastUpdate].[YYMay],

dbo_MasterSalesForecast_Test.[YY:06]=[MasterSalesForecastUpdate].[YYJun],

dbo_MasterSalesForecast_Test.[YY:07]=[MasterSalesForecastUpdate].[YYJul])

,

IIF(Month(Date())=”6″,(SET

dbo_MasterSalesForecast_Test.[YY:06]=[MasterSalesForecastUpdate].[YYJun],

dbo_MasterSalesForecast_Test.[YY:07]=[MasterSalesForecastUpdate].[YYJul],

dbo_MasterSalesForecast_Test.[YY:08]=[MasterSalesForecastUpdate].[YYAug])

,

IIF(Month(Date())=”7″,(SET

dbo_MasterSalesForecast_Test.[YY:07]=[MasterSalesForecastUpdate].[YYJul],

dbo_MasterSalesForecast_Test.[YY:08]=[MasterSalesForecastUpdate].[YYAug],

dbo_MasterSalesForecast_Test.[YY:09]=[MasterSalesForecastUpdate].[YYSep])

,

IIF(Month(Date())=”8″,(SET

dbo_MasterSalesForecast_Test.[YY:08]=[MasterSalesForecastUpdate].[YYAug],

dbo_MasterSalesForecast_Test.[YY:09]=[MasterSalesForecastUpdate].[YYSep],

dbo_MasterSalesForecast_Test.[YY:10]=[MasterSalesForecastUpdate].[YYOct])

,

IIF(Month(Date())=”9″,(SET

dbo_MasterSalesForecast_Test.[YY:09]=[MasterSalesForecastUpdate].[YYSep],

dbo_MasterSalesForecast_Test.[YY:10]=[MasterSalesForecastUpdate].[YYOct],

dbo_MasterSalesForecast_Test.[YY:11]=[MasterSalesForecastUpdate].[YYNov])

,

IIF(Month(Date())=”10″,(SET

dbo_MasterSalesForecast_Test.[YY:10]=[MasterSalesForecastUpdate].[YYOct],

dbo_MasterSalesForecast_Test.[YY:11]=[MasterSalesForecastUpdate].[YYNov],

dbo_MasterSalesForecast_Test.[YY:12]=[MasterSalesForecastUpdate].[YYDec])

,

IIF(Month(Date())=”11″,(SET

dbo_MasterSalesForecast_Test.[YY:11]=[MasterSalesForecastUpdate].[YYNov],

dbo_MasterSalesForecast_Test.[YY:12]=[MasterSalesForecastUpdate].[YYDec],

dbo_MasterSalesForecast_Test.[YY+1:01]=[MasterSalesForecastUpdate].[YY+1Jan])

,

IIF(Month(Date())=”12″(SET

dbo_MasterSalesForecast_Test.[YY:12]=[MasterSalesForecastUpdate].[YYDec],

dbo_MasterSalesForecast_Test.[YY+1:01]=[MasterSalesForecastUpdate].[YY+1Jan],

dbo_MasterSalesForecast_Test.[YY+1:02]=[MasterSalesForecastUpdate].[YY+1Feb])

)))))))))));

submitted by /u/frolord81
[link] [comments]