Unexpected error message with an INNER JOIN UPDATE

1

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?

2

Yes. Either a trigger or a different location in the code. The code you've shown doesn't have a subquery.

3

Of course. I didn't design the DB but a trigger is likely. That being the case the best solution that I can think of is to wrap my code into a stored procedure that handles one ID at a time & place that within a WHILE loop.

I've stayed away from loops in SQL but is that that most practical resolution?

4

You can run this to check for trigger(s):

EXEC [DB1].sys.sp_helptrigger bvc_PACKAGE

I guess you'd have to do one at a time, if you can't address the issue(s) in the trigger(s).

5

@SergioM, if it is a trigger it sounds like it is not written correctly. A trigger (which you may know) needs to handle multiple records at once, but some people tend to think of one record at a time.

6

@djj55 I tend to think of all database concepts as needing to handle many rows at once. The architect of this database loves the one row-at-a-time concept, which I hate.

That being said, there's not much I can do. I'm hesitant to use loops in SQL but that's pretty much the corner he painted us into. Thanks for the observation though.