Hi All,

I'm new to T-SQL coming from a VFP background. I have a table with a number of columns State1, State2, State3 etc that I want to update via an "UPDATE RESULTS SET Statenn = (select SUM(MTDSALES) FROM SALES where STATE = @lnState)". Is it possible for me to refer to the column name programmatically so that I can run the Update state inside a WHILE loop ?

ie something like

SET @lnState = 1
WHILE @lnState < 10
lcState = 'STATE' + CAST(@lnState AS CHAR)
BEGIN
UPDATE RESULTS
SET &lcState = (select SUM(MTDSALES) FROM SALES where STATE = @lnState)
SET @lnState = @lnState + 1
END


Tia,

Andy