Link to home
Start Free TrialLog in
Avatar of gogetsome
gogetsomeFlag for United States of America

asked on

Help with update with join

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)))
SOLUTION
Avatar of Ephraim Wangoya
Ephraim Wangoya
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of gogetsome

ASKER

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...
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.