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
|