October 4, 2012 at 4:22 pm
Please help me to update
update tblAverageMonthlyConsumption
set region=(select distinct A.region from tblgas01g4c40
as A where tblaveragemonthlyConsumption.id_no=a.id_no)
Please help me to update the
When executing the above command The following message will
please help me:
Msg 512, Level 16, State 1, Line 1
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.
October 4, 2012 at 4:28 pm
Table tblgas01g4c40 is returning more than one distinct record for that ID. There's nothing that can be 'fixed' in the statement, you're returning too much data in your subselect.
Either clean the data, find a more selective way to join the tables, or you'll have to use a MAX/MIN/SUM/whatever function on the field to make sure it can only return one result.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 5, 2012 at 12:35 am
It seems strange that the FK on the tblgas has the same name as the Average consumption table, is this really the join? but it could be true but as we dont have the DDL for the tables concerned were guessing.
Firstly I would run this query to see what the problem is
Select
gas.Region
,AMC.Region
,AMC.Id_no
,gas.Id_no
From tblaveragemonthlyConsumption AMC
JOIN tblgas01g4c40 gas on AMC.id_no=gas.id_no
Ideally you want to minimise the number of updates going on as updates are one of the more expensive types, and by adding a WHERE AMC.Retion!=gas.Region will do this as really you only want to update items that are not the same.
As for the update I've restructured it a little, but it still needs work and it will be dependant on the data and business rules around handling id_no's with the differnt regions assigned.
update
AMC
set
region=a.Region
From tblaveragemonthlyConsumption AMC
JOIN tblgas01g4c40 gas on AMC.id_no=gas.id_no
where
Amc.Region!=a.Region
One possible improvement is to do the following,
update
AMC
set
Region=a.Region
From tblaveragemonthlyConsumption AMC
JOIN (Select id_no,Max(Region) Region
From tblgas01g4c40
group by Id_no) gas on AMC.id_no=gas.id_no
where
Amc.Region!=a.Region
In the end the choice of singling out which row in the tblgas is used has to be down to you as we cant see the data or know the business rules, especially as you dont say what the datatype on the region is.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
October 5, 2012 at 5:13 am
update tblAverageMonthlyConsumption
SET REGION=(SELECT TOP 1 BB.REGION FROM tblgas01g4c40 BB WHERE tblAverageMonthlyConsumption.ID_NO=BB.ID_NO)
October 5, 2012 at 5:45 am
carlosaamaral (10/5/2012)
update tblAverageMonthlyConsumptionSET REGION=(SELECT TOP 1 BB.REGION FROM tblgas01g4c40 BB WHERE tblAverageMonthlyConsumption.ID_NO=BB.ID_NO)
That is is a flawed way to approach the solution, as every time you run it there is no guarantee which order the data will be coming back so you will have the issue of data discrepancies each time its run.
The other problem with this is that you will hit every row in the Average monthly Consumption table, and if there is no row in the tblGas for a given ID it will update it with a null, regardless of the existing data.
The Update with an INNER JOIN and WHERE is by far the most effiecent way of updating in these cases, as you will only ever hit the rows that have a corresponding row in the tblgas table and where the data is actually different.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
October 5, 2012 at 8:11 am
Jason-299789 (10/5/2012)
The Update with an INNER JOIN and WHERE is by far the most effiecent way of updating in these cases, as you will only ever hit the rows that have a corresponding row in the tblgas table and where the data is actually different.
YOU ARE absolutely right ..
Can not guarantee the integrity of the table tblgas01g4c40
well, this is better than distinct
thank you..
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply