April 15, 2009 at 2:46 pm
Hello, I was wondering if anyone could help with this T-SQL problem that is stumping me.
Here is the current UPDATE statement that works; the subselect only returns 1 result and updates StorageFee properly:
UPDATE #MSFO
SET StorageFee =
(SELECT CONVERT(NUMERIC(11, 2), FS.StorageMonthFee)
FROM [DB].[dbo].[SFS] AS FS
WHERE FS.StorageFeeScheduleID = #MSFO.StorageFeeScheduleID
AND FS.StorageFeeContractType = #MSFO.ContractTypeCode
AND FS.StorageFeeCategoryCode = #MSFO.CategoryCode
AND FS.StorageFeeProductCode = #MSFO.ProductCode
AND FS.StorageFeeCurrency = #MSFO.Currency
AND FS.StorageFeeVehicle = #MSFO.VehicleForSchedule
AND DATEPART(month, FS.ContractShipEndDate) = DATEPART(month, #MSFO.OriginalShippingEndDate)
AND DATEPART(month, FS.StorageMonthDate) = DATEPART(month, #MSFO.ShippedMonth)
AND ABS(DATEDIFF(month, #MSFO.OriginalShippingEndDate, #MSFO.ShippedMonth)) < 12
AND FS.StorageMonthDate <> @maxstoragedate)
I am trying to rewrite this using a variable, like this:
DECLARE @N1 NUMERIC(11, 2)
SET @N1 = (SELECT CONVERT(NUMERIC(11, 2), FS.StorageMonthFee)
FROM [DB].[dbo].[SFS] AS FS, #MSFO AS MS
WHERE FS.StorageFeeScheduleID = MS.StorageFeeScheduleID
AND FS.StorageFeeContractType = MS.ContractTypeCode
AND FS.StorageFeeCategoryCode = MS.CategoryCode
AND FS.StorageFeeProductCode = MS.ProductCode
AND FS.StorageFeeCurrency = MS.Currency
AND FS.StorageFeeVehicle = MS.VehicleForSchedule
AND DATEPART(month, FS.ContractShipEndDate) = DATEPART(month, MS.OriginalShippingEndDate)
AND DATEPART(month, FS.StorageMonthDate) = DATEPART(month, MS.ShippedMonth)
AND ABS(DATEDIFF(month, MS.OriginalShippingEndDate, MS.ShippedMonth)) < 12
AND FS.StorageMonthDate <> @maxstoragedate)
UPDATE #MSFO
SET StorageFee = @N1
However, the second portion returns an error when I try to run it:
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, = or when the subquery is used as an expression."
My question is, are these two SELECTS not the same thing? Isn't a subselect in an UPDATE technically just joining the two tables the same as I am trying to do in the second portion of code?
Any help would be appreciated, thanks!
April 15, 2009 at 3:30 pm
I've tried to simplify the situation to see more clearly what is going on.
CREATE TABLE #MSFO (StorageFee numeric(11, 2))
GO
INSERT INTO #MSFO VALUES(1.0)
GO
CREATE TABLE #FS (StorageMonthFee numeric(11, 2))
GO
INSERT INTO #FS VALUES (2.0)
INSERT INTO #FS VALUES (3.0)
GO
Given the above data, both of the following TSQL statements throw the error message: "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, = or when the subquery is used as an expression."
SELECT (SELECT #FS.StorageMonthFee FROM #FS)
UPDATE #MSFO SET StorageFee = (SELECT #FS.StorageMonthFee FROM #FS)
However, both of the following return without error. The SELECT statement returns 2 rows. The UPDATE statement updates the single row in the #MSFO table with the value 2.0, the value of the StorageMonthFee column from the first row in the #FS table.
SELECT (SELECT #FS.StorageMonthFee) FROM #FS
UPDATE #MSFO SET StorageFee = (SELECT #FS.StorageMonthFee) FROM #FS
I think the reason that the second set of statements do not throw an error is that the subquery expression (SELECT #FS.StorageMonthFee) materializes as a single scalar value whereas the subquery expression (SELECT #FS.StorageMonthFee FROM #FS) returns a rowset with two rows.
April 15, 2009 at 3:38 pm
adonavon (4/15/2009)
Hello, I was wondering if anyone could help with this T-SQL problem that is stumping me.Here is the current UPDATE statement that works; the subselect only returns 1 result and updates StorageFee properly:
UPDATE #MSFO
SET StorageFee =
(SELECT CONVERT(NUMERIC(11, 2), FS.StorageMonthFee)
FROM [DB].[dbo].[SFS] AS FS
WHERE FS.StorageFeeScheduleID = #MSFO.StorageFeeScheduleID
AND FS.StorageFeeContractType = #MSFO.ContractTypeCode
AND FS.StorageFeeCategoryCode = #MSFO.CategoryCode
AND FS.StorageFeeProductCode = #MSFO.ProductCode
AND FS.StorageFeeCurrency = #MSFO.Currency
AND FS.StorageFeeVehicle = #MSFO.VehicleForSchedule
AND DATEPART(month, FS.ContractShipEndDate) = DATEPART(month, #MSFO.OriginalShippingEndDate)
AND DATEPART(month, FS.StorageMonthDate) = DATEPART(month, #MSFO.ShippedMonth)
AND ABS(DATEDIFF(month, #MSFO.OriginalShippingEndDate, #MSFO.ShippedMonth)) < 12
AND FS.StorageMonthDate <> @maxstoragedate)
The "subselect" or rather correlated subquery almost certainly returns more than one row but the UPDATE...FROM syntax doesn't throw an error. One of the values from the "subselect" will update the StorageFee value(s). If the values in each row in the subselect are different, then you're taking pot luck on which one(s) it will be.
To demonstrate this, try the following code:
SELECT CONVERT(NUMERIC(11, 2), FS.StorageMonthFee)
FROM #MSFO, [DB].[dbo].[SFS] AS FS
WHERE FS.StorageFeeScheduleID = #MSFO.StorageFeeScheduleID
AND FS.StorageFeeContractType = #MSFO.ContractTypeCode
AND FS.StorageFeeCategoryCode = #MSFO.CategoryCode
AND FS.StorageFeeProductCode = #MSFO.ProductCode
AND FS.StorageFeeCurrency = #MSFO.Currency
AND FS.StorageFeeVehicle = #MSFO.VehicleForSchedule
AND DATEPART(month, FS.ContractShipEndDate) = DATEPART(month, #MSFO.OriginalShippingEndDate)
AND DATEPART(month, FS.StorageMonthDate) = DATEPART(month, #MSFO.ShippedMonth)
AND ABS(DATEDIFF(month, #MSFO.OriginalShippingEndDate, #MSFO.ShippedMonth)) < 12
AND FS.StorageMonthDate <> @maxstoragedate
This query is almost a regular UPDATE...FROM - but not quite. Here's a modification of the above which would be more readily recognisable to most lurkers here, more so if the old-style join syntax were updated:
UPDATE m SET StorageFee = CONVERT(NUMERIC(11, 2), FS.StorageMonthFee)
FROM #MSFO m, [DB].[dbo].[SFS] AS FS
WHERE FS.StorageFeeScheduleID = m.StorageFeeScheduleID
AND FS.StorageFeeContractType = m.ContractTypeCode
AND FS.StorageFeeCategoryCode = m.CategoryCode
AND FS.StorageFeeProductCode = m.ProductCode
AND FS.StorageFeeCurrency = m.Currency
AND FS.StorageFeeVehicle = m.VehicleForSchedule
AND DATEPART(month, FS.ContractShipEndDate) = DATEPART(month, m.OriginalShippingEndDate)
AND DATEPART(month, FS.StorageMonthDate) = DATEPART(month, m.ShippedMonth)
AND ABS(DATEDIFF(month, m.OriginalShippingEndDate, m.ShippedMonth)) < 12
AND FS.StorageMonthDate <> @maxstoragedate)
I think you would agree that if it does what you have in mind, it has an advantage over the "subselect" method in that it is readily testable.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply