I have a update query shown as below. But it's giving me a error message "Subquery returned more than 1 value".
SET RETIREMENT_PLAN =
(SELECT MAX(Retirement_plan) FROM #TEMP_ACCOUNTING1 Acc1
WHERE Acc1.PAY_PERIOD_ENDING_DATE < Acc2.PAY_PERIOD_ENDING_DATE
and Acc1.ID= Acc2.ID)
FROM #TEMP_ACCOUNTING1 Acc2
WHERE Retirement_Plan IS NULL)
My data looks like...
ID PAY_PERIOD_ENDING_DATE Retirement_plan Employee Name
1 02/16/2010 k Tom
1 01/16/2010 k Tom
2 02/16/2010 2 King
2 01/16/2010 2 King
So basically my subquery does return more than one record for each "ID".
I want to update the "Retirement_plan " for all the PAY_PERIOD_ENDING_DATE of that "ID".
How can I achieve this task?