Postgres Pro
Downloads
Home   >   mailing lists

Thread: Update question

Update question

From
"Fay Du"
Date:
<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Hi all:</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I have a table like this:</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Table cost</span></font><p class="MsoNormal"><span class="GramE"><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">Id<span style="mso-spacerun:yes"> </span><span
class="SpellE">edge</span></span></font></span><spanclass="SpellE"><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:
Arial">_id</span></font></span><font face="Arial" size="2"><span style="font-size:
10.0pt;font-family:Arial"><span style="mso-spacerun:yes">  </span><span
style="mso-spacerun:yes">  </span>cost</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">1<span style="mso-spacerun:yes">       </span>30<span style="mso-spacerun:yes">        
</span>101.4</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">2<span style="mso-spacerun:yes">       </span>30<span style="mso-spacerun:yes">         
</span>null</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">3<span style="mso-spacerun:yes">       </span>40<span style="mso-spacerun:yes">         
</span>500.2</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">4<span style="mso-spacerun:yes">       </span>40<span style="mso-spacerun:yes">          
</span>null</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">5<span style="mso-spacerun:yes">        </span>45<span style="mso-spacerun:yes">         
</span>300.7</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">6<span style="mso-spacerun:yes">        </span>45<span style="mso-spacerun:yes">          
</span>null</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I want to set cost value with same <span class="SpellE">edge_id</span> by same value. The existing
valuesare calculated by calling a function (calculate edge length). I can calculate the value by same function, but the
functionwas slow. I wander if I can use update statement to set the second value with same <span
class="SpellE">edge_id</span>.Thanks in advance for your help.</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Fay</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font></div>

Re: Update question

From
Markus Schaber
Date:
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

-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org

Re: Update question

From
Gábriel Ákos
Date:
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            =-

Re: Update question

From
"Fay Du"
Date:
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