I am attempting to do do an INNER JOIN UPDATE and am encountering an unexpected message
My SQL looks like this:
UPDATE pac SET FinalShippingFee = CAST( ( dhlr.RateFee * (1 + sccf.ShippingRate_Percent ) ) AS DECIMAL(18,2)) FROM [DB1].[dbo].[bvc_PACKAGE] pac INNER JOIN [DB1].[dbo].[bvc_Order] o ON pac.OrderID = o.id FULL JOIN [DB2].[dbo].[SC_Shipping_Fees] scsf ON O.ID = scsf.SC_OrderID INNER JOIN [DB2].[dbo].[SC_Company_Fees] sccf ON o.CompanyID = sccf.SC_CompanyID INNER JOIN [DB2].[dbo].[DHL_Ratecard] dhlr ON dhlr.WeightOzs = o.ShippingWeightTotalOz WHERE sccf.DateExpired > GETDATE() AND dhlr.ShippingService = o.ShippingMethodSelected AND (scsf.CalculatedInflatedFee IS NULL OR scsf.CalculatedActualFee != dhlr.RateFee)
If I run a select statement on the same criteria, I only get unique rows. However when I run the update statement I get the following error.
Msg 512, Level 16, State 1, Procedure tg_bvc_Package, Line 13
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.
Given that, I'm not sure how to do a basic update. Is there something else I should be looking out for?