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
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.