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. ************************************************************************