Re: Update question - Mailing list pgsql-sql

From Fay Du
Subject Re: Update question
Date
Msg-id 00c301c65296$0c21c6b0$b8d849c6@versaterm.com
Whole thread Raw
In response to Re: Update question  (Gábriel Ákos <akos.gabriel@i-logic.hu>)
List pgsql-sql
Markus and Gábriel:
Thank you very much for the help.
Yes, the table is redundant. What I am trying to do is using pgdijstra
module from postGIS which is on the top of postgreSQL. And the module
asks for entire network is directional or non-directional. But my
network part is directional and other part is non-directional. I made it
is directional by duplicate all non-directional edges into 2 directional
edges and it is where redundant come from. And the module only accept
data from one table, so I have to keep all data in one table.

Thanks again.
Fay

-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Gábriel Ákos
Sent: Tuesday, March 28, 2006 12:34 PM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Update question

Markus Schaber wrote:
> Hi, Fay,
>
> Fay Du wrote:
>> Table cost
>>
>> Id  edge_id    cost
>> 1       30         101.4
>> 2       30          null
>> 3       40          500.2
>> 4       40           null
>> 5        45          300.7
>> 6        45           null
>
>> I want to set cost value with same edge_id by same value. The
existing
>> values are calculated by calling a function (calculate edge length).
I
>> can calculate the value by same function, but the function was slow.
I
>> wander if I can use update statement to set the second value with
same
>> edge_id. Thanks in advance for your help.
>
> It should go like:
>
> UPDATE cost SET edge_id = (SELECT cost FROM cost innr WHERE
innr.edge_id
> = edge_id AND innr.cost is not null) WHERE cost is null;
>
> HTH,
> Marku
>

This is ok, but this is redundant, i guess. you should put edge_id and
cost into another table and use join to retrieve cost for each id.
put a key on edge_id, most likely a primary (unique) key.


--
Üdvözlettel,
Gábriel Ákos
-=E-Mail :akos.gabriel@i-logic.hu|Web:  http://www.i-logic.hu=-
-=Tel/fax:+3612367353            |Mobil:+36209278894            =-


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate      subscribe-nomail command to
majordomo@postgresql.orgso that your      message can get through to the mailing list cleanly 



pgsql-sql by date:

Previous
From: Gábriel Ákos
Date:
Subject: Re: Update question
Next
From: "TJ O'Donnell"
Date:
Subject: Re: Bitfields always atomic? Other way to store attributes?