Re: [SQL] UPDATE WITH ORDER BY

[Rodrigo Carvalhaes]

Thanksyou and Franz for your help. Simple and efficient... I was blind.... The plpgsql "for" is the perfect solution It was great. Have a nice week!!! Cheers, Rodrigo Carvalhaes Christoph Haller wrote: 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 ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- Abraço, Rodrigo Carvalhaes DBA PostgreSQL Moderador grupo siga-br -- Esta mensagem foi verificada pelo sistema de antivírus e acredita-se estar livre de perigo.