Re: UPDATE WITH ORDER BY

From: Rodrigo Carvalhaes <grupos(at)carvalhaes(dot)net>
To: Christoph Haller <ch(at)rodos(dot)fzk(dot)de>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: UPDATE WITH ORDER BY
Date: 2005-04-26 16:07:18
Message-ID: 426E6736.8030403@carvalhaes.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(dot)318032AA(at)rodos(dot)fzk(dot)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(at)postgresql(dot)org">majordomo(at)postgresql(dot)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>

Attachment Content-Type Size
unknown_filename text/html 3.2 KB

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Lord Knight of the Black Rose 2005-04-26 20:18:01 can someone jelp me on this?
Previous Message Christoph Haller 2005-04-26 13:27:44 Re: people who buy A, also buy C, D, E