pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUPDATE WITH ORDER BY
6+ messages / 5 participants
[nested] [flat]
* UPDATE WITH ORDER BY
@ 2005-04-26 03:12 Rodrigo Carvalhaes <grupos@carvalhaes.net>
2005-04-26 09:08 ` Re: UPDATE WITH ORDER BY Christoph Haller <ch@rodos.fzk.de>
2005-04-27 12:05 ` Re: UPDATE WITH ORDER BY Greg Sabino Mullane <greg@turnstep.com>
0 siblings, 2 replies; 6+ messages in thread
From: Rodrigo Carvalhaes @ 2005-04-26 03:12 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<div class="moz-text-html" lang="x-western">
<tt>Hi Guys!<br>
<br>
I need to make an UPDATE on a column reordering it with a sequence
using order by a description.<br>
Confusing??? Well.. Let me give an example...<br>
<br>
Today, my table it's organized like this:<br>
<br>
Code / Description<br>
9 Orange<br>
15 Apple<br>
1 Pear<br>
3 Tomato<br>
<br>
I wanna to reorganize (reordering the code from 1 to ... ordering by
description)<br>
</tt><tt><br>
Code / Description<br>
</tt><tt>1 Apple</tt><br>
<tt>2 Orange<br>
3 Pear<br>
4 Tomato</tt><br>
<tt><br>
I created a sequence but I am having no succes to use it because UPDATE
don't accept ORDER BY CLAUSE. The "ideal SQL" is UPDATE table SET code
= nextval('sequence') ORDER BY description <br>
<br>
I searched a lot on the NET without ant "tip" for my case. <br>
It's a very simple need but I am not able to solve it...<br>
<br>
Anyone knows how I can do it?<br>
<br>
Cheers,<br>
<br>
-- <br>
Rodrigo Carvalhaes<br>
<br>
</tt><!-- |**|end egp html banner|**| -->
</div>
</body>
<br />--
<br />Esta mensagem foi verificada pelo sistema de antivírus e
<br /> acredita-se estar livre de perigo.
</html>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: UPDATE WITH ORDER BY
2005-04-26 03:12 UPDATE WITH ORDER BY Rodrigo Carvalhaes <grupos@carvalhaes.net>
@ 2005-04-26 09:08 ` Christoph Haller <ch@rodos.fzk.de>
2005-04-26 16:07 ` Re: UPDATE WITH ORDER BY Rodrigo Carvalhaes <grupos@carvalhaes.net>
1 sibling, 1 reply; 6+ messages in thread
From: Christoph Haller @ 2005-04-26 09:08 UTC (permalink / raw)
To: grupos@carvalhaes.net; +Cc: pgsql-sql
> Rodrigo Carvalhaes wrote:
>
> Hi Guys!
>
> I need to make an UPDATE on a column reordering it with a sequence
> using order by a description.
> Confusing??? Well.. Let me give an example...
>
> Today, my table it's organized like this:
>
> Code / Description
> 9 Orange
> 15 Apple
> 1 Pear
> 3 Tomato
>
> I wanna to reorganize (reordering the code from 1 to ... ordering by
> description)
>
> Code / Description
> 1 Apple
> 2 Orange
> 3 Pear
> 4 Tomato
>
> I created a sequence but I am having no succes to use it because
> UPDATE don't accept ORDER BY CLAUSE. The "ideal SQL" is UPDATE table
> SET code = nextval('sequence') ORDER BY description
>
> I searched a lot on the NET without ant "tip" for my case.
> It's a very simple need but I am not able to solve it...
>
> Anyone knows how I can do it?
>
> Cheers,
>
> --
> Rodrigo Carvalhaes
>
I doubt this can be done by a single SQL command.
My approach is a function. I did:
CREATE TABLE fruittable(
fruitcode INTEGER,
fruitname TEXT
);
INSERT INTO fruittable VALUES( 9, 'Orange' );
INSERT INTO fruittable VALUES( 15, 'Apple' );
INSERT INTO fruittable VALUES( 1, 'Pear' );
INSERT INTO fruittable VALUES( 3, 'Tomato' );
SELECT * FROM fruittable ORDER BY fruitname ;
fruitcode | fruitname
-----------+-----------
15 | Apple
9 | Orange
1 | Pear
3 | Tomato
CREATE OR REPLACE FUNCTION reorder_fruitcode() RETURNS INTEGER AS '
DECLARE
newcode INTEGER ;
fruitrecord RECORD ;
BEGIN
newcode := 1 ;
FOR fruitrecord IN SELECT * FROM fruittable ORDER BY fruitname LOOP
RAISE NOTICE ''fruitname is %'', fruitrecord.fruitname ;
UPDATE fruittable SET fruitcode = newcode
WHERE fruitname = fruitrecord.fruitname ;
newcode := newcode + 1 ;
END LOOP ;
RETURN 1;
END;
' LANGUAGE plpgsql;
SELECT reorder_fruitcode();
SELECT * FROM fruittable ORDER BY fruitname ;
fruitcode | fruitname
-----------+-----------
1 | Apple
2 | Orange
3 | Pear
4 | Tomato
(4 rows)
Voila.
Regards, Christoph
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: UPDATE WITH ORDER BY
2005-04-26 03:12 UPDATE WITH ORDER BY Rodrigo Carvalhaes <grupos@carvalhaes.net>
2005-04-26 09:08 ` Re: UPDATE WITH ORDER BY Christoph Haller <ch@rodos.fzk.de>
@ 2005-04-26 16:07 ` Rodrigo Carvalhaes <grupos@carvalhaes.net>
0 siblings, 0 replies; 6+ messages in thread
From: Rodrigo Carvalhaes @ 2005-04-26 16:07 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; +Cc: pgsql-sql
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-2" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Thanksyou and Franz for your help. Simple and efficient... I was
blind.... The plpgsql "for" is the perfect solution<br>
<br>
It was great. Have a nice week!!!<br>
<br>
Cheers,<br>
<br>
Rodrigo Carvalhaes<br>
<br>
Christoph Haller wrote:
<blockquote cite="mid426E04FD.318032AA@rodos.fzk.de" type="cite">
<blockquote type="cite">
<pre wrap="">Rodrigo Carvalhaes wrote:
Hi Guys!
I need to make an UPDATE on a column reordering it with a sequence
using order by a description.
Confusing??? Well.. Let me give an example...
Today, my table it's organized like this:
Code / Description
9 Orange
15 Apple
1 Pear
3 Tomato
I wanna to reorganize (reordering the code from 1 to ... ordering by
description)
Code / Description
1 Apple
2 Orange
3 Pear
4 Tomato
I created a sequence but I am having no succes to use it because
UPDATE don't accept ORDER BY CLAUSE. The "ideal SQL" is UPDATE table
SET code = nextval('sequence') ORDER BY description
I searched a lot on the NET without ant "tip" for my case.
It's a very simple need but I am not able to solve it...
Anyone knows how I can do it?
Cheers,
--
Rodrigo Carvalhaes
</pre>
</blockquote>
<pre wrap=""><!---->I doubt this can be done by a single SQL command.
My approach is a function. I did:
CREATE TABLE fruittable(
fruitcode INTEGER,
fruitname TEXT
);
INSERT INTO fruittable VALUES( 9, 'Orange' );
INSERT INTO fruittable VALUES( 15, 'Apple' );
INSERT INTO fruittable VALUES( 1, 'Pear' );
INSERT INTO fruittable VALUES( 3, 'Tomato' );
SELECT * FROM fruittable ORDER BY fruitname ;
fruitcode | fruitname
-----------+-----------
15 | Apple
9 | Orange
1 | Pear
3 | Tomato
CREATE OR REPLACE FUNCTION reorder_fruitcode() RETURNS INTEGER AS '
DECLARE
newcode INTEGER ;
fruitrecord RECORD ;
BEGIN
newcode := 1 ;
FOR fruitrecord IN SELECT * FROM fruittable ORDER BY fruitname LOOP
RAISE NOTICE ''fruitname is %'', fruitrecord.fruitname ;
UPDATE fruittable SET fruitcode = newcode
WHERE fruitname = fruitrecord.fruitname ;
newcode := newcode + 1 ;
END LOOP ;
RETURN 1;
END;
' LANGUAGE plpgsql;
SELECT reorder_fruitcode();
SELECT * FROM fruittable ORDER BY fruitname ;
fruitcode | fruitname
-----------+-----------
1 | Apple
2 | Orange
3 | Pear
4 | Tomato
(4 rows)
Voila.
Regards, Christoph
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to <a class="moz-txt-link-abbreviated" href="mailto:majordomo@postgresql.org">majordomo@postgresql.org</a> so that your
message can get through to the mailing list cleanly
</pre>
</blockquote>
<br>
<pre class="moz-signature" cols="72">--
Abraço,
Rodrigo Carvalhaes
DBA PostgreSQL
Moderador grupo siga-br</pre>
</body>
<br />--
<br />Esta mensagem foi verificada pelo sistema de antivírus e
<br /> acredita-se estar livre de perigo.
</html>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: UPDATE WITH ORDER BY
2005-04-26 03:12 UPDATE WITH ORDER BY Rodrigo Carvalhaes <grupos@carvalhaes.net>
@ 2005-04-27 12:05 ` Greg Sabino Mullane <greg@turnstep.com>
1 sibling, 0 replies; 6+ messages in thread
From: Greg Sabino Mullane @ 2005-04-27 12:05 UTC (permalink / raw)
To: pgsql-sql
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
(Please send email as text, not HTML)
> I need to make an UPDATE on a column reordering it with a sequence
> using order by a description
> ...
BEGIN;
CREATE SEQUENCE fruit_seq;
CREATE TABLE newfruit AS SELECT nextval('fruit_seq')::int AS newid, * FROM fruit ORDER BY lower(description);
ALTER TABLE newfruit DROP COLUMN id;
ALTER TABLE newfruit RENAME COLUMN newid TO id;
DROP TABLE fruit;
ALTER TABLE newfruit RENAME TO fruit;
DROP SEQUENCE fruit_seq;
COMMIT;
SELECT * FROM fruit ORDER BY id ASC;
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200504270805
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iD8DBQFCb4ArvJuQZxSWSsgRAnSGAJwMbp6qoN3H2wFedsgn8N55kV6zUQCg77Qn
VWsBmACCUFIdzRDRRalG6KI=
=y3G9
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: UPDATE WITH ORDER BY
@ 2005-04-26 06:58 Ramakrishnan Muralidharan <ramakrishnanm@pervasive-postgres.com>
0 siblings, 0 replies; 6+ messages in thread
From: Ramakrishnan Muralidharan @ 2005-04-26 06:58 UTC (permalink / raw)
To: grupos@carvalhaes.net; +Cc: pgsql-sql
Hi,
To my knowledge it is not possible to sort and update the code in a single update statement. I have done it through a simple function. I have given the function below.
CREATE OR REPLACE FUNCTION SortCode()
RETURNS INT4 AS $$
DECLARE
rRec RECORD;
BEGIN
PERFORM SETVAL( 'test1_code_seq' , 1 , false );
FOR rRec IN (SELECT * FROM TEST1 ORDER BY DESCRIPTION) LOOP
UPDATE TEST1 SET CODE = nextval( 'test1_code_seq' ) WHERE DESCRIPTION = rRec.DESCRIPTION;
END LOOP;
RETURN 0;
END;
$$ language 'plpgsql';
following is the data used for testing
create table test1( code serial , description varchar( 25 ) )
insert into test1 values( 9,'Orange');
insert into test1 values(15,'Apple');
insert into test1 values(1,'Pear');
insert into test1 values(3,'Tomato');
Regards,
R.Muralidharan
-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Rodrigo Carvalhaes
Sent: Tuesday, April 26, 2005 8:43 AM
Cc: pgsql-sql@postgresql.org
Subject: [SQL] UPDATE WITH ORDER BY
Hi Guys!
I need to make an UPDATE on a column reordering it with a sequence using order by a description.
Confusing??? Well.. Let me give an example...
Today, my table it's organized like this:
Code / Description
9 Orange
15 Apple
1 Pear
3 Tomato
I wanna to reorganize (reordering the code from 1 to ... ordering by description)
Code / Description
1 Apple
2 Orange
3 Pear
4 Tomato
I created a sequence but I am having no succes to use it because UPDATE don't accept ORDER BY CLAUSE. The "ideal SQL" is UPDATE table SET code = nextval('sequence') ORDER BY description
I searched a lot on the NET without ant "tip" for my case.
It's a very simple need but I am not able to solve it...
Anyone knows how I can do it?
Cheers,
--
Rodrigo Carvalhaes
--
Esta mensagem foi verificada pelo sistema de antivírus e
acredita-se estar livre de perigo.
^ permalink raw reply [nested|flat] 6+ messages in thread
* UPDATE with ORDER BY
@ 2008-02-19 12:49 Robins Tharakan <tharakan@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Robins Tharakan @ 2008-02-19 12:49 UTC (permalink / raw)
To: pgsql-sql
Hi,
I know this kind of a question is asked earlier, but I couldn't find an
answer there (in the previous round of posting).
Instead of wanting to update the first record in an UPDATE .. ORDER BY
condition, (because of triggers that act downward) what I want is that all
records be updated, but in a given order, for e.g. ascending on date.
Is a FOR LOOP my best bet or is something like UPDATE .... ORDER BY in the
offing ?
Thanks
*Robins*
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2008-02-19 12:49 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-26 03:12 UPDATE WITH ORDER BY Rodrigo Carvalhaes <grupos@carvalhaes.net>
2005-04-26 09:08 ` Christoph Haller <ch@rodos.fzk.de>
2005-04-26 16:07 ` Rodrigo Carvalhaes <grupos@carvalhaes.net>
2005-04-27 12:05 ` Greg Sabino Mullane <greg@turnstep.com>
2005-04-26 06:58 Re: UPDATE WITH ORDER BY Ramakrishnan Muralidharan <ramakrishnanm@pervasive-postgres.com>
2008-02-19 12:49 UPDATE with ORDER BY Robins Tharakan <tharakan@gmail.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