April 2, 2008 at 8:36 am
HI I have a query - below---
UPDATE GOLD_BROKER_OUTPUT
SET MAT_GWP = (
SELECT
sum(
case
WHEN dbo.tblOneStop_Detail.DAT_YEAR_MONTH BETWEEN 200703 AND 200802 THENdbo.tblOneStop_Detail.GWP
ELSE 0
END)
FROM
GOLD_BROKER_OUTPUT LEFT JOIN tblOneStop_Detail ON
GOLD_BROKER_OUTPUT.Agencies = tblOneStop_Detail.AGY_ACCOUNT_REF AND
GOLD_BROKER_OUTPUT.TRADE_CDE = tblOneStop_Detail.TUS_KEYAND
GOLD_BROKER_OUTPUT.BUS_CDE = tblOneStop_Detail.PRD_BUS_LINE_CD
group by GOLD_BROKER_OUTPUT.Agencies,GOLD_BROKER_OUTPUT.BUS_CDE,GOLD_BROKER_OUTPUT.TRADE_CDE
)
This is updating a field in a table of 736,600 row of data - after around 7 mins I get the folowing 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.
The statement has been terminated.
I've tried changing the "and" part to "or" but just runs and runs.
anyone know why I am getting this message - and how to rectify
Any assistance would greatly appreciated
Regards
April 2, 2008 at 8:43 am
Ian,
Doing a quick scan not sure if this will help but your date parameters for your BETWEEN are not in quotes
Jeff
April 2, 2008 at 9:23 am
Do you realize your statement is trying to update every record in GOLD_BROKER_OUTPUT to the same value?
If you do, the problem is in the GROUP BY in your subquery. Since your subquery is returning multiple values, you cannot use it on the right side of the assignment statement (SET).
If you do not, look at this (because it is what you have)
UPDATE GOLD_BROKER_OUTPUT
SET MAT_GWP = (1)
If your subquery were to return '1' (noting that it must return a single row for your update to work), this is what you would get. It is an update with no WHERE clause.
The reference to GOLD_BROKER_OUTPUT in your subquery does not make this some kind of correlated sub-query update.
April 2, 2008 at 9:24 am
HI Jeff
the field is numeric that contains the date parts - I tried another variation just not getting the required result --
UPDATE GOLD_BROKER_OUTPUT
SET MAT_GWP = (
SELECT GOLD_BROKER_OUTPUT.Agencies,
GOLD_BROKER_OUTPUT.BUS_CDE,
GOLD_BROKER_OUTPUT.TRADE_CDE,
sum (dbo.tblOneStop_Detail.GWP) AS TotalGWP
FROM GOLD_BROKER_OUTPUT
LEFT JOIN tblOneStop_Detail on
GOLD_BROKER_OUTPUT.Agencies = tblOneStop_Detail.AGY_ACCOUNT_REF
/*
LEFT JOIN tblOneStop_Detail ON
GOLD_BROKER_OUTPUT.Agencies = tblOneStop_Detail.AGY_ACCOUNT_REF AND
GOLD_BROKER_OUTPUT.TRADE_CDE = tblOneStop_Detail.TUS_KEYAND
GOLD_BROKER_OUTPUT.BUS_CDE = tblOneStop_Detail.PRD_BUS_LINE_CD
*/
WHERE dbo.tblOneStop_Detail.DAT_YEAR_MONTH BETWEEN 200703 AND 200802
group by GOLD_BROKER_OUTPUT.Agencies,
GOLD_BROKER_OUTPUT.BUS_CDE,
GOLD_BROKER_OUTPUT.TRADE_CDE
)
This does return data but totals rather than total grouped up against the agency - bus cde and trade code
Regards
April 2, 2008 at 9:58 am
Ian,
Read and answer Michael's response need to confirm what you are trying to accomplish on the SET =
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply