You don't seem to be positioning on a row in the Parameter table with a
WHERE clause.
Ellis Robin (Bundaberg) wrote:
Could I please get some help on the syntax required to perform my UPDATE
based on a selection from multiple tables? I've been through the
archives but can't seem to make much sense of the examples given.
I have a table containing 'new' paramater values, I need to update the
relevant records in the 'existing' parameter table, however my selection
is based on a handful of tables. Here's the selection I run to view my
old and new parameters side by side, this works fine:
select Scenario.ScenarioID, Scenario.NetworkID, Link.LinkID,
Catchment.CatchmentID,Catchment.Name, FunctionalUnit.FunctionalUnitID,
FunctionalUnitDefinition.Name, StandardFU.RainfallRunoffModelID,
Parameter.Parameter, Parameter.ParameterValue, NewParams.parametervalue
from Scenario, Link, Catchment, FunctionalUnit,
FunctionalUnitDefinition, StandardFU, Parameter, NewParams where
Scenario.ScenarioID = 1004 and Scenario.NetworkID = Link.NetworkID and
Link.LinkID = Catchment.LinkID and FunctionalUnit.CatchmentID =
Catchment.CatchmentID and FunctionalUnit.FunctionalUnitDefinitionID =
FunctionalUnitDefinition.FunctionalUnitDefinitionID and
FunctionalUnit.FunctionalUnitID = StandardFU.StandardFUID and
StandardFU.RainfallRunoffModelID = Parameter.ModelID and Catchment.Name
= NewParams.Subcatchments and FunctionalUnitDefinition.Name =
NewParams.FU_name and Parameter.Parameter = NewParams.parameter
However when I use this selection to update the relevent field in the
parameters table I don't get a 'correct' update, instead I get the first
value encountered in the NewParams table over writing ALL of the old
parameter values. Here's my syntax:
Update Parameter set ParameterValue = (select NewParams.parametervalue
from Scenario, Link, Catchment, FunctionalUnit,
FunctionalUnitDefinition, StandardFU, Parameter, NewParams where
Scenario.ScenarioID = 1004 and Scenario.NetworkID = Link.NetworkID and
Link.LinkID = Catchment.LinkID and FunctionalUnit.CatchmentID =
Catchment.CatchmentID and FunctionalUnit.FunctionalUnitDefinitionID =
FunctionalUnitDefinition.FunctionalUnitDefinitionID and
FunctionalUnit.FunctionalUnitID = StandardFU.StandardFUID and
StandardFU.RainfallRunoffModelID = Parameter.ModelID and Catchment.Name
= NewParams.Subcatchments and FunctionalUnitDefinition.Name =
NewParams.FU_name and Parameter.Parameter = NewParams.parameter)
Can anyone shed some light on this for me? I've tried adding more WHERE
statements after the nested selection, but with no better results.
Thanks
Rob
Robin Ellis
Natural Resources & Water
PO Box 1167
Bundaberg QLD 4670
Ph: +617 4131 5771
Fax: +617 4131 5823
************************************************************************
The information in this email together with any attachments is
intended only for the person or entity to which it is addressed
and may contain confidential and/or privileged material.
Any form of review, disclosure, modification, distribution
and/or publication of this email message is prohibited, unless
as a necessary part of Departmental business.
If you have received this message in error, you are asked to
inform the sender as quickly as possible and delete this message
and any copies of this message from your computer and/or your
computer system network.
************************************************************************
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
