Thanks John, that's what I thought too... My positioning on a row is dependent on 2 values, and without any better knowledge of SQLite I've tried implementing this:
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) Where Parameter.ModelID = (select Parameter.ModelID 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) and Parameter.Parameter = (select Parameter.Parameter 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 my results are the same with this attempt at row positioning. Both of the select statements used in the WHERE expression do retturn valid records. Any more hints? Rob -----Original Message----- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Friday, 8 June 2007 2:48 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Syntax help with UPDATE in SQLite Database Browser 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] ------------------------------------------------------------------------ ----- ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
