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 '

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