Re: UPDATE WITH ORDER BY - Mailing list pgsql-sql

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 />
ChristophHaller wrote: <blockquote cite="mid426E04FD.318032AA@rodos.fzk.de" type="cite"><blockquote type="cite"><pre
wrap="">RodrigoCarvalhaes 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 '

DECLAREnewcode INTEGER ;fruitrecord RECORD ;

BEGINnewcode := 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
messagecan 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><br />-- <br />Esta mensagem foi verificada pelo sistema de antivírus e <br /> acredita-se
estarlivre de perigo.