X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 4A9925372E for ; Wed, 27 Apr 2005 06:13:58 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 90757-07 for ; Wed, 27 Apr 2005 09:13:55 +0000 (GMT) Received: from ausimss.pervasive.com (ausimss.pervasive.com [66.45.103.246]) by svr1.postgresql.org (Postfix) with ESMTP id 66A8953717 for ; Wed, 27 Apr 2005 06:13:50 -0300 (ADT) Received: from ausbayes1.aus.pervasive.com ([172.16.8.6]) by ausimss.pervasive.com with InterScan Messaging Security Suite; Wed, 27 Apr 2005 04:13:42 -0500 Received: from ausmail2k3.aus.pervasive.com ([172.16.4.8]) by ausbayes1.aus.pervasive.com with Microsoft SMTPSVC(5.0.2195.6713); Wed, 27 Apr 2005 04:13:39 -0500 Received: from BLRMAIL1.aus.pervasive.com ([10.3.16.60]) by ausmail2k3.aus.pervasive.com with Microsoft SMTPSVC(5.0.2195.6713); Wed, 27 Apr 2005 04:13:38 -0500 Subject: Re: UPDATE WITH ORDER BY Date: Tue, 26 Apr 2005 12:28:46 +0530 Message-ID: <02767D4600E59A4487233B23AEF5C5992A4080@blrmail1.aus.pervasive.com> X-MS-Has-Attach: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C54A2D.641C623D" X-MS-TNEF-Correlator: Thread-Topic: [SQL] UPDATE WITH ORDER BY Thread-Index: AcVKDp1JOPLewss4SEGh6uvx11C7ugAHAfIA X-MimeOLE: Produced By Microsoft Exchange V6.0.6603.0 content-class: urn:content-classes:message From: "Ramakrishnan Muralidharan" To: Cc: X-OriginalArrivalTime: 27 Apr 2005 09:13:38.0117 (UTC) FILETIME=[6597E750:01C54B09] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.485 tagged_above=0 required=5 tests=AWL, DATE_IN_PAST_24_48, HTML_50_60, HTML_MESSAGE, HTML_TITLE_EMPTY, SUBJ_ALL_CAPS X-Spam-Level: X-Archive-Number: 200504/407 X-Sequence-Number: 21300 This is a multi-part message in MIME format. ------_=_NextPart_001_01C54A2D.641C623D Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, =20 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.=20 =20 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 =3D nextval( 'test1_code_seq' ) WHERE = DESCRIPTION =3D rRec.DESCRIPTION; END LOOP;=20 RETURN 0; END; $$ language 'plpgsql'; following is the data used for testing =20 create table test1( code serial , description varchar( 25 ) ) =20 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 =20 -----Original Message----- From: pgsql-sql-owner@postgresql.org = [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Rodrigo Carvalhaes Sent: Tuesday, April 26, 2005 8:43 AM Cc: pgsql-sql@postgresql.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 = =3D nextval('sequence') ORDER BY description=20 I searched a lot on the NET without ant "tip" for my case.=20 It's a very simple need but I am not able to solve it... Anyone knows how I can do it? Cheers, --=20 Rodrigo Carvalhaes --=20 Esta mensagem foi verificada pelo sistema de antiv=EDrus e=20 acredita-se estar livre de perigo.=20 ------_=_NextPart_001_01C54A2D.641C623D Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
         = Hi,
 
          &nbs= p; To my=20 knowledge it is not possible to sort and update the code in a single = update=20 statement. I have done it through a simple function. I have given the = function=20 below.
 
CREATE=20 OR REPLACE FUNCTION SortCode()
RETURNS INT4 AS = $$
DECLARE
  rRec=20 RECORD;
BEGIN
  PERFORM SETVAL( 'test1_code_seq' , 1 , false=20 );
  FOR rRec IN  (SELECT * FROM TEST1 ORDER BY = DESCRIPTION) =20 LOOP
    UPDATE TEST1 SET CODE =3D nextval( = 'test1_code_seq' )=20 WHERE DESCRIPTION =3D rRec.DESCRIPTION;
  END LOOP;
  = RETURN=20 0;
END;
$$ language 'plpgsql';
following is the data used for testing
 
create=20 table test1( code serial , description varchar( 25 ) = )
 
insert=20 into test1 values( 9,'Orange');
insert into test1=20 values(15,'Apple');
insert into test1 values(1,'Pear');
insert = into test1=20 values(3,'Tomato');
Regards,
R.Muralidharan
 
-----Original Message-----
From:=20 pgsql-sql-owner@postgresql.org = [mailto:pgsql-sql-owner@postgresql.org]On=20 Behalf Of Rodrigo Carvalhaes
Sent: Tuesday, April 26, = 2005 8:43=20 AM
Cc: pgsql-sql@postgresql.org
Subject: [SQL] = UPDATE WITH=20 ORDER BY

Hi Guys!

I need = to make an=20 UPDATE on a column reordering it with a sequence using order by a=20 description.
Confusing??? Well.. Let me give an = example...

Today, my=20 table it's organized like this:

Code     /=20 Description
9          = Orange
15        =20 Apple
1         =20 Pear
3         =20 Tomato

I wanna to reorganize (reordering the code from 1 to ... = ordering by description)

Code     = /=20 Description
1 =         =20 = Apple
2         = =20 Orange
3          = Pear
4=20          = Tomato

I=20 created a sequence but I am having no succes to use it because UPDATE = don't=20 accept ORDER BY CLAUSE. The "ideal SQL" is UPDATE table SET code =3D=20 nextval('sequence') ORDER BY description

I searched a lot on = the NET=20 without ant "tip" for my case.
It's a very simple need but I am = not able=20 to solve it...

Anyone knows how I can do = it?

Cheers,

--=20
Rodrigo=20 Carvalhaes


--=20
Esta mensagem foi verificada pelo sistema de antiv=EDrus e =
acredita-se=20 estar livre de perigo.
------_=_NextPart_001_01C54A2D.641C623D--