Re: UPDATE WITH ORDER BY

> 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 '

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