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 majordomo@postgresql.org so that your
      message can get through to the mailing list cleanly

  

-- 


Abraço,

Rodrigo Carvalhaes
DBA PostgreSQL
Moderador grupo siga-br