> 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