Subquery returned more than 1 value

Hello,

I have a update query shown as below. But it's giving me a error message "Subquery returned more than 1 value".
UPDATE #TEMP_ACCOUNTING1
SET RETIREMENT_PLAN =
(SELECT
(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?
Please assist.

Thank you
angel7170Asked:
Who is Participating?
 
Om PrakashConnect With a Mentor Commented:
Try:
UPDATE Acc2
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)
0
 
angel7170Author Commented:
It's giving me an error message

Invalid Object name 'ACC2'
0
 
Om PrakashCommented:
This works on my machine but it does not update anything for the data given by you
UPDATE Acc2
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

Open in new window

0
 
Om PrakashConnect With a Mentor Commented:
I want to update the "Retirement_plan " for all the PAY_PERIOD_ENDING_DATE of that "ID".

update Retirement_plan to what value?
Also, you have selected   MAX(Retirement_plan), which contains text and numeric values.

what exactly needs to be updated and based on what condition?
0
 
angel7170Author Commented:
No comments
0
All Courses

From novice to tech pro — start learning today.