Re: UPDATE WITH ORDER BY

Hi,

To my knowledge it is not possible to sort and update the code in a single update statement. I have done it through a simple function. I have given the function below.

CREATE OR REPLACE FUNCTION SortCode()
RETURNS INT4 AS $$
DECLARE
rRec RECORD;
BEGIN
PERFORM SETVAL( 'test1_code_seq' , 1 , false );
FOR rRec IN (SELECT * FROM TEST1 ORDER BY DESCRIPTION) LOOP
UPDATE TEST1 SET CODE = nextval( 'test1_code_seq' ) WHERE DESCRIPTION = rRec.DESCRIPTION;
END LOOP;
RETURN 0;
END;
$$ language 'plpgsql';

following is the data used for testing

create table test1( code serial , description varchar( 25 ) )

insert into test1 values( 9,'Orange');
insert into test1 values(15,'Apple');
insert into test1 values(1,'Pear');
insert into test1 values(3,'Tomato');

Regards,
R.Muralidharan

-----Original Message-----
From: pgsql-sql-owner(at)postgresql(dot)org [mailto:pgsql-sql-owner(at)postgresql(dot)org]On Behalf Of Rodrigo Carvalhaes
Sent: Tuesday, April 26, 2005 8:43 AM
Cc: pgsql-sql(at)postgresql(dot)org
Subject: [SQL] UPDATE WITH ORDER BY

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

--
Esta mensagem foi verificada pelo sistema de antivírus e
acredita-se estar livre de perigo.