Maybe you really have to loop "outside" sqlite to align the rows & values. From the result you got and the UPDATE documentation, I can guess that the subselect in the assignment is flattened to a scalar. Unfortunately sqlite does not have something like
update tbl1 set col=tbl2.col from tbl2 where tbl2.id=tbl1.id which would have been what you needed. Cheers, M. Manese On 6/8/07, Ellis Robin (Bundaberg) <[EMAIL PROTECTED]> wrote:
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
----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------