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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy