pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: [NOVICE] update question
11+ messages / 7 participants
[nested] [flat]
* Re: [NOVICE] update question
@ 2002-10-08 15:39 Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 11+ messages in thread
From: Josh Berkus @ 2002-10-08 15:39 UTC (permalink / raw)
To: DAVID KUCHARSKI <dave@iemco.com>; josh@agliodbs.com; +Cc: pgsql-sql
David,
I'm sorry, you just don't seem to be at the "self-help" stage. I
strongly reccommend that you hire a database consultant to help you.
-Josh Berkus
DAVID KUCHARSKI <dave@iemco.com> wrote:
> the result set of the view is 3628 lines. It would be
> nice if postgres could do this update instead of our
> data entry person.
>
>
> Josh Berkus wrote:
> >
> > David,
> >
> > > > > > 1) Is there any way you can easily identify the relevant
> rows in the
> > > > legacy
> > > > > > database and export only those?
> > >
> > > I guess I'm unclear as to which table you are
> > > referring to as the LEGACY table. either way i
> > > imagine that by looking at a table or VIEW of the pik
> > > numbers common to both tables is what i need, so yes I
> > > can get that. in fact i have now. but how can i use
> > > that table to update the price fields in inventory
> >
> > No, I'm talking about the database system that is exporting to
> text. Can you
> > tell it to export only the rows you need?
> >
> > --
> > -Josh Berkus
> > Aglio Database Solutions
> > San Francisco
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* update question
@ 2005-12-05 22:59 Matthew Peter <survivedsushi@yahoo.com>
2005-12-05 23:08 ` Re: update question Jaime Casanova <systemguards@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Matthew Peter @ 2005-12-05 22:59 UTC (permalink / raw)
To: pgsql-sql
I need a throw away value for an insert statement... example...
update table
set value = 1, value = 2, throw_away_value -- so i don't break the query
where id = 1;
Thanks
---------------------------------
Yahoo! Personals
Let fate take it's course directly to your email.
See who's waiting for you Yahoo! Personals
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: update question
2005-12-05 22:59 update question Matthew Peter <survivedsushi@yahoo.com>
@ 2005-12-05 23:08 ` Jaime Casanova <systemguards@gmail.com>
2005-12-05 23:41 ` Re: update question Matthew Peter <survivedsushi@yahoo.com>
2005-12-05 23:43 ` Re: update question Matthew Peter <survivedsushi@yahoo.com>
0 siblings, 2 replies; 11+ messages in thread
From: Jaime Casanova @ 2005-12-05 23:08 UTC (permalink / raw)
To: Matthew Peter <survivedsushi@yahoo.com>; +Cc: pgsql-sql
On 12/5/05, Matthew Peter <survivedsushi@yahoo.com> wrote:
> I need a throw away value for an insert statement... example...
>
> update table
> set value = 1, value = 2, throw_away_value -- so i don't break the query
> where id = 1;
>
> Thanks
>
What do you mean by "throw away value"?
are you trying to update and if the row doesn't exist then insert? if
that is you can do a function in plpgsql and use exceptions for
that... there is an example in the manuals
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: update question
2005-12-05 22:59 update question Matthew Peter <survivedsushi@yahoo.com>
2005-12-05 23:08 ` Re: update question Jaime Casanova <systemguards@gmail.com>
@ 2005-12-05 23:41 ` Matthew Peter <survivedsushi@yahoo.com>
2005-12-06 00:33 ` Re: update question Jaime Casanova <systemguards@gmail.com>
2005-12-06 05:45 ` Re: update question Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 11+ messages in thread
From: Matthew Peter @ 2005-12-05 23:41 UTC (permalink / raw)
To: Jaime Casanova <systemguards@gmail.com>; +Cc: pgsql-sql
it's in a loop so there's an extra comma at the end so i was thinking i could put in a throw away value to keep the update from breaking if there's an additional comma
Jaime Casanova <systemguards@gmail.com> wrote: On 12/5/05, Matthew Peter wrote:
> I need a throw away value for an insert statement... example...
>
> update table
> set value = 1, value = 2, throw_away_value -- so i don't break the query
> where id = 1;
>
> Thanks
>
What do you mean by "throw away value"?
are you trying to update and if the row doesn't exist then insert? if
that is you can do a function in plpgsql and use exceptions for
that... there is an example in the manuals
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
---------------------------------
Yahoo! Personals
Single? There's someone we'd like you to meet.
Lots of someones, actually. Yahoo! Personals
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: update question
2005-12-05 22:59 update question Matthew Peter <survivedsushi@yahoo.com>
2005-12-05 23:08 ` Re: update question Jaime Casanova <systemguards@gmail.com>
2005-12-05 23:41 ` Re: update question Matthew Peter <survivedsushi@yahoo.com>
@ 2005-12-06 00:33 ` Jaime Casanova <systemguards@gmail.com>
1 sibling, 0 replies; 11+ messages in thread
From: Jaime Casanova @ 2005-12-06 00:33 UTC (permalink / raw)
To: Matthew Peter <survivedsushi@yahoo.com>; +Cc: pgsql-sql
On 12/5/05, Matthew Peter <survivedsushi@yahoo.com> wrote:
> it's in a loop so there's an extra comma at the end so i was thinking i
> could put in a throw away value to keep the update from breaking if there's
> an additional comma
>
> Jaime Casanova <systemguards@gmail.com> wrote:
> On 12/5/05, Matthew Peter wrote:
> > I need a throw away value for an insert statement... example...
> >
> > update table
> > set value = 1, value = 2, throw_away_value -- so i don't break the query
> > where id = 1;
> >
> > Thanks
> >
>
i guess you are constructing the query in a string and EXECUTEd it...
if that is the case review your logic...
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: update question
2005-12-05 22:59 update question Matthew Peter <survivedsushi@yahoo.com>
2005-12-05 23:08 ` Re: update question Jaime Casanova <systemguards@gmail.com>
2005-12-05 23:41 ` Re: update question Matthew Peter <survivedsushi@yahoo.com>
@ 2005-12-06 05:45 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 11+ messages in thread
From: Tom Lane @ 2005-12-06 05:45 UTC (permalink / raw)
To: Matthew Peter <survivedsushi@yahoo.com>; +Cc: Jaime Casanova <systemguards@gmail.com>; pgsql-sql
Matthew Peter <survivedsushi@yahoo.com> writes:
> it's in a loop so there's an extra comma at the end so i was thinking i could put in a throw away value to keep the update from breaking if there's an additional comma
The best thing is to fix your loop logic. Usually it's not hard to emit
a comma only if one is needed.
If you're intent on using a broken loop, you could do "foo = foo" where
foo is any table column you didn't yet assign to. Consider though the
corner case where you've already assigned all the columns. Best bet
is to fix your loop...
regards, tom lane
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: update question
2005-12-05 22:59 update question Matthew Peter <survivedsushi@yahoo.com>
2005-12-05 23:08 ` Re: update question Jaime Casanova <systemguards@gmail.com>
@ 2005-12-05 23:43 ` Matthew Peter <survivedsushi@yahoo.com>
1 sibling, 0 replies; 11+ messages in thread
From: Matthew Peter @ 2005-12-05 23:43 UTC (permalink / raw)
To: Jaime Casanova <systemguards@gmail.com>; +Cc: pgsql-sql
Like WHERE 1 = 1, but in UPDATE table SET value = 1, 1 = 1;
---------------------------------
Yahoo! Personals
Single? There's someone we'd like you to meet.
Lots of someones, actually. Try Yahoo! Personals
^ permalink raw reply [nested|flat] 11+ messages in thread
* Update question
@ 2006-03-28 16:41 Fay Du <fay.du@versaterm.com>
2006-03-28 16:51 ` Re: Update question Markus Schaber <schabi@logix-tt.com>
0 siblings, 1 reply; 11+ messages in thread
From: Fay Du @ 2006-03-28 16:41 UTC (permalink / raw)
To: pgsql-sql
Hi all:
I have a table like this:
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.
Fay
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Update question
2006-03-28 16:41 Update question Fay Du <fay.du@versaterm.com>
@ 2006-03-28 16:51 ` Markus Schaber <schabi@logix-tt.com>
2006-03-28 17:34 ` Re: Update question Gábriel Ákos <akos.gabriel@i-logic.hu>
0 siblings, 1 reply; 11+ messages in thread
From: Markus Schaber @ 2006-03-28 16:51 UTC (permalink / raw)
To: Fay Du <fay.du@versaterm.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Update question
2006-03-28 16:41 Update question Fay Du <fay.du@versaterm.com>
2006-03-28 16:51 ` Re: Update question Markus Schaber <schabi@logix-tt.com>
@ 2006-03-28 17:34 ` Gábriel Ákos <akos.gabriel@i-logic.hu>
2006-03-28 18:33 ` Re: Update question Fay Du <fay.du@versaterm.com>
0 siblings, 1 reply; 11+ messages in thread
From: Gábriel Ákos @ 2006-03-28 17:34 UTC (permalink / raw)
To: pgsql-sql
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 =-
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Update question
2006-03-28 16:41 Update question Fay Du <fay.du@versaterm.com>
2006-03-28 16:51 ` Re: Update question Markus Schaber <schabi@logix-tt.com>
2006-03-28 17:34 ` Re: Update question Gábriel Ákos <akos.gabriel@i-logic.hu>
@ 2006-03-28 18:33 ` Fay Du <fay.du@versaterm.com>
0 siblings, 0 replies; 11+ messages in thread
From: Fay Du @ 2006-03-28 18:33 UTC (permalink / raw)
To: 'Gábriel Ákos' <akos.gabriel@i-logic.hu>; 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.org so that your
message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2006-03-28 18:33 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-10-08 15:39 Re: [NOVICE] update question Josh Berkus <josh@agliodbs.com>
2005-12-05 22:59 update question Matthew Peter <survivedsushi@yahoo.com>
2005-12-05 23:08 ` Jaime Casanova <systemguards@gmail.com>
2005-12-05 23:41 ` Matthew Peter <survivedsushi@yahoo.com>
2005-12-06 00:33 ` Jaime Casanova <systemguards@gmail.com>
2005-12-06 05:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-12-05 23:43 ` Matthew Peter <survivedsushi@yahoo.com>
2006-03-28 16:41 Update question Fay Du <fay.du@versaterm.com>
2006-03-28 16:51 ` Re: Update question Markus Schaber <schabi@logix-tt.com>
2006-03-28 17:34 ` Re: Update question Gábriel Ákos <akos.gabriel@i-logic.hu>
2006-03-28 18:33 ` Re: Update question Fay Du <fay.du@versaterm.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox