pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to update record in a specified order
5+ messages / 3 participants
[nested] [flat]
* How to update record in a specified order
@ 2002-08-09 21:21 Jean-Luc Lachance <jllachan@nsd.ca>
0 siblings, 2 replies; 5+ messages in thread
From: Jean-Luc Lachance @ 2002-08-09 21:21 UTC (permalink / raw)
To: pgsql-sql
Hi all,
I want to update a field with a 'NEXTVAL', but I want the record updated
in a specific order.
Any simple way of doing this other than having to create a temp table?
JLL
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to update record in a specified order
@ 2002-08-09 21:37 Josh Berkus <josh@agliodbs.com>
parent: Jean-Luc Lachance <jllachan@nsd.ca>
1 sibling, 1 reply; 5+ messages in thread
From: Josh Berkus @ 2002-08-09 21:37 UTC (permalink / raw)
To: Jean-Luc Lachance <jllachan@nsd.ca>; +Cc: pgsql-sql
JLL,
> I want to update a field with a 'NEXTVAL', but I want the record updated
> in a specific order.
> Any simple way of doing this other than having to create a temp table?
Please be more speciifc. What do you mean, "specified order"?
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to update record in a specified order
@ 2002-08-09 21:59 Jean-Luc Lachance <jllachan@nsd.ca>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 5+ messages in thread
From: Jean-Luc Lachance @ 2002-08-09 21:59 UTC (permalink / raw)
To: josh@agliodbs.com; +Cc: pgsql-sql
As in an order by clause... If it existed.
Josh Berkus wrote:
>
> JLL,
>
> > I want to update a field with a 'NEXTVAL', but I want the record updated
> > in a specific order.
> > Any simple way of doing this other than having to create a temp table?
>
> Please be more speciifc. What do you mean, "specified order"?
>
> --
> -Josh Berkus
> Aglio Database Solutions
> San Francisco
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: 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] 5+ messages in thread
* Re: How to update record in a specified order
@ 2002-08-09 22:02 Dan Langille <dan@langille.org>
parent: Jean-Luc Lachance <jllachan@nsd.ca>
1 sibling, 0 replies; 5+ messages in thread
From: Dan Langille @ 2002-08-09 22:02 UTC (permalink / raw)
To: Jean-Luc Lachance <jllachan@nsd.ca>; +Cc: pgsql-sql
On 9 Aug 2002 at 14:37, Josh Berkus wrote:
> JLL,
>
> > I want to update a field with a 'NEXTVAL', but I want the record updated
> > in a specific order.
> > Any simple way of doing this other than having to create a temp table?
>
> Please be more speciifc. What do you mean, "specified order"?
My reading of what is required:
- sort a given result set according to some criteria
- then do something like UPDATE table SET myfield = NEXTVAL
- each row in the result set would get a value one more than the
previous row
--
Dan Langille
I'm looking for a computer job:
http://www.freebsddiary.org/dan_langille.php
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to update record in a specified order
@ 2002-08-09 22:12 Josh Berkus <josh@agliodbs.com>
parent: Jean-Luc Lachance <jllachan@nsd.ca>
0 siblings, 0 replies; 5+ messages in thread
From: Josh Berkus @ 2002-08-09 22:12 UTC (permalink / raw)
To: Jean-Luc Lachance <jllachan@nsd.ca>; +Cc: pgsql-sql
JLL,
So, you want to update a field with a NEXTVAL counter, but with the counter
ordered by another column?
If so, you will have to use a procedure. Ordering your UPDATEs is not part
of SQL -- it requires a procedural element. Here's a simple procedure (you
debug it):
CREATE PROCEDURE add_my_table_counter ()
RETURNS TEXT AS '
DECLARE v_rec RECORD;
BEGIN
WHILE v_rec IN SELECT * FROM my_table ORDER BY last_name LOOP
UPDATE my_table SET counter_field = NEXTVAL(''my_sequence'')
WHERE my_table.id = v_rec.id;
END LOOP;
RETURN ''Done updating.'';
END;'
LANGUAGE 'plpgsql';
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2002-08-09 22:12 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-08-09 21:21 How to update record in a specified order Jean-Luc Lachance <jllachan@nsd.ca>
2002-08-09 21:37 ` Josh Berkus <josh@agliodbs.com>
2002-08-09 21:59 ` Jean-Luc Lachance <jllachan@nsd.ca>
2002-08-09 22:12 ` Josh Berkus <josh@agliodbs.com>
2002-08-09 22:02 ` Dan Langille <dan@langille.org>
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