November 28, 2011 at 8:36 am
Hi,
I need to update temporary table ‘@tmpOutput’ using a reference table ‘Haulage_Rate’.
Table ‘@tmpOutput’ contains several fields including:
ContractDate, ItemId, StartDate, EndDate, W_Loc, CollectedGoods, HaulageRate
2010-10-01, P12000, 2010-11-01, 2010-11-30, CUP, 0, 0.00
2010-10-05, P12000, 2010-11-01, 2010-11-30, CUP, 0, 0.00
2010-12-14, P12000, 2011-05-01, 2011-05-31, CUP, 0, 0.00
2011-06-23, P12000, 2011-10-01, 2011-10-31, CUP, 0, 0.00
2011-08-16, P12000, 2011-11-01, 2011-11-30, CUP, 0, 0.00
Table Haulage_Rate is structured as follows:
Item, Destination, Start_Date, End_Date, Cost, Source
P12000, CUP, 01/05/2010, 31/12/2010, 7.50, Glasgow
P12000, CUP, 01/01/2011, 31/12/2012, 8.10, Glasgow
I am using the following update query:
SET DATEFORMAT YMD
UPDATE @tmpOutput
SET HaulageRate =
CASE CollectGoods
WHEN 0
THEN (SELECT hr.Cost FROM Market_Prices.dbo.Market_Prices_Haulage_Rate hr LEFT JOIN
@tmpOutput t1 ON
t1.ContractDate BETWEEN hr.Start_Date AND hr.End_Date
AND t1.W_house = hr.Destination
AND t1.ItemId = hr.Item)
ELSE 0.00
END;
I'm expecting the 1st 3 records to be Updated as 7.50
and the last 2 to be Updated as 8.10
But I’m getting the following message:
"Msg 512
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
Can anyone help please.
Thanks in advance,
November 28, 2011 at 8:42 am
You have more than one value of Cost in Market_Prices_Haulage_Rate for each value of HaulageRate in @tmpOutput - that's what it's saying. Which one do you want to use for the update?
John
November 28, 2011 at 8:50 am
Guess based on your data -
BEGIN TRAN
--First, let's build some sample data
DECLARE @tmpOutput AS TABLE (ContractDate DATETIME, ItemId CHAR(6), StartDate DATETIME,
EndDate DATETIME, W_Loc CHAR(3), CollectedGoods INT, HaulageRate MONEY)
INSERT INTO @tmpOutput
SELECT '2010-10-01', 'P12000', '2010-11-01', '2010-11-30', 'CUP', 0, 0.00
UNION ALL SELECT '2010-10-05', 'P12000', '2010-11-01', '2010-11-30', 'CUP', 0, 0.00
UNION ALL SELECT '2010-12-14', 'P12000', '2011-05-01', '2011-05-31', 'CUP', 0, 0.00
UNION ALL SELECT '2011-06-23', 'P12000', '2011-10-01', '2011-10-31', 'CUP', 0, 0.00
UNION ALL SELECT '2011-08-16', 'P12000', '2011-11-01', '2011-11-30', 'CUP', 0, 0.00
DECLARE @Haulage_Rate AS TABLE (Item CHAR(6), Destination CHAR(3), Start_Date DATETIME,
End_Date DATETIME, Cost MONEY, Source CHAR(7))
INSERT INTO @Haulage_Rate
SELECT 'P12000', 'CUP', '2010-05-01', '2010-12-31', 7.50, 'Glasgow'
UNION ALL SELECT 'P12000', 'CUP', '2011-01-01', '2012-12-31', 8.10, 'Glasgow'
--Check to see it's all as displayed by the OP
SELECT * FROM @tmpOutput
--Update table guess
UPDATE t1
SET HaulageRate = hr.Cost
FROM @tmpOutput t1
LEFT OUTER JOIN @Haulage_Rate hr ON t1.ContractDate BETWEEN hr.Start_Date AND hr.End_Date AND t1.ItemId = hr.Item
--Check to see what has been updated
SELECT * FROM @tmpOutput
ROLLBACK
It ends up with the following: -
ContractDate ItemId StartDate EndDate W_Loc CollectedGoods HaulageRate
----------------------- ------ ----------------------- ----------------------- ----- -------------- ---------------------
2010-10-01 00:00:00.000 P12000 2010-11-01 00:00:00.000 2010-11-30 00:00:00.000 CUP 0 7.50
2010-10-05 00:00:00.000 P12000 2010-11-01 00:00:00.000 2010-11-30 00:00:00.000 CUP 0 7.50
2010-12-14 00:00:00.000 P12000 2011-05-01 00:00:00.000 2011-05-31 00:00:00.000 CUP 0 7.50
2011-06-23 00:00:00.000 P12000 2011-10-01 00:00:00.000 2011-10-31 00:00:00.000 CUP 0 8.10
2011-08-16 00:00:00.000 P12000 2011-11-01 00:00:00.000 2011-11-30 00:00:00.000 CUP 0 8.10
November 28, 2011 at 9:09 am
Many thanks John and Cadavre for your help.
Cadavre - on first glance that looks correct.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply