RE: [sqlite] Syntax help with UPDATE in SQLite Database Browser

2007-06-07 Thread Ellis Robin (Bundaberg) 
Thanks Miguel, I fear you're right. For this infrequent update process
it just may just be easier to throw the data into another db format.

Thanks again

Rob

-Original Message-
From: miguel manese [mailto:[EMAIL PROTECTED] 
Sent: Friday, 8 June 2007 3:31 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Syntax help with UPDATE in SQLite Database Browser

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]

-



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]
-

Re: [sqlite] Syntax help with UPDATE in SQLite Database Browser

2007-06-07 Thread miguel manese 

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]
-

RE: [sqlite] Syntax help with UPDATE in SQLite Database Browser

2007-06-07 Thread Ellis Robin (Bundaberg) 
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.FunctionalU

Re: [sqlite] Syntax help with UPDATE in SQLite Database Browser

2007-06-07 Thread John Stanton 
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]
-

[sqlite] Syntax help with UPDATE in SQLite Database Browser

2007-06-07 Thread Ellis Robin (Bundaberg) 
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.