Help with update with join

gogetsome
gogetsome used Ask the Experts™
on
Hello, i need some help with the following statement

UPDATE #TempMyDCR
SET MileageAmountPaid =  (E.MileageTotal * (SELECT M.CostPerMile FROM Mileage AS M, Jobs AS J , Timesheets AS T WHERE M.ClientID = J.ClientID  AND      J.JobNumber = T.JobNumber       AND T.TimeSheetId in (Select TimesheetId From #TempMyDCR)))
FROM [Expenses] AS E Join #TempMyDCR AS D
On D.TimeSheetID = E.TimeSheetID

Grateful for any help!


I'm getting the following error:
Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

Most likely the result of this:
(E.MileageTotal * (SELECT M.CostPerMile FROM Mileage AS M, Jobs AS J , Timesheets AS T WHERE M.ClientID = J.ClientID  AND      J.JobNumber = T.JobNumber       AND T.TimeSheetId in (Select TimesheetId From #TempMyDCR)))
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
Ephraim WangoyaSoftware Engineer
Commented:
try
UPDATE #TempMyDCR
SET MileageAmountPaid = (E.MileageTotal * (SELECT M.CostPerMile 
                                           FROM Mileage M 
                                           inner join Jobs J on M.ClientID = J.ClientID 
                                           inner join Timesheets T on J.JobNumber = T.JobNumber
                                           where T.TimeSheetId = #TempMyDCR.TimesheetId))
FROM [Expenses] AS E Join #TempMyDCR AS D
On D.TimeSheetID = E.TimeSheetID

Open in new window

gogetsome

Author

Commented:
That did not work. It said that #tempmyscr.timesheet could not be bound.

This did work:

UPDATE a
SET MileageAmountPaid = (E.MileageTotal * (SELECT M.CostPerMile
                                           FROM Mileage M
                                           inner join Jobs J on M.ClientID = J.ClientID
                                           inner join Timesheets T on J.JobNumber = T.JobNumber
                                           where T.TimeSheetId = a.TimesheetId))
FROM [Expenses] AS E Join #TempMyDCR AS a
On a.TimeSheetID = E.TimeSheetID

But I'm not sure if the result is correct as there will be many rows where milageamountpaid is being updated with this:

(E.MileageTotal * (SELECT M.CostPerMile
                                           FROM Mileage M
                                           inner join Jobs J on M.ClientID = J.ClientID
                                           inner join Timesheets T on J.JobNumber = T.JobNumber
                                           where T.TimeSheetId = a.TimesheetId))

I'm not so good with joins involving many tables...
Software Engineer
Commented:

That should be correct,
You will get multiple updates depending on how many records are returned by the joins
gogetsome

Author

Commented:
Thank you so much! Today I have to rework a stored procedure I inherited where instead of updating #TempMyDCR that had only one row to one that now contains many. It is ugly to say the least.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial