[askdba] Re: Update using sequences

From: <sanjay.khangarot@xxxxxxxxx>
To: <askdba@xxxxxxxxxxxxx>
Date: Tue, 26 Oct 2004 09:23:57 +0530
in 9i you can do in this fashion
I have created a temporary table and inserted dummy values in it..

blck>create table temp (x number,y number);
Table created.

blck>create sequence seq_x start with 1 increment by 1;
Sequence created.

blck>insert into temp values(&1,&2)
  2  /
Enter value for 1: 10
Enter value for 2: 2
old   1: insert into temp values(&1,&2)
new   1: insert into temp values(10,2)

1 row created.

blck>/
Enter value for 1: 30
Enter value for 2: 4
old   1: insert into temp values(&1,&2)
new   1: insert into temp values(30,4)

1 row created.

blck>/
Enter value for 1: 20
Enter value for 2: 3
old   1: insert into temp values(&1,&2)
new   1: insert into temp values(20,3)

1 row created.

here is the query to update in the order of x=0D

blck>update (select x,y from temp order by x)
  2  set y=3Dseq_x.nextval;

3 rows updated.

blck>select * from temp;

         X          Y
---------- ----------
        10          1
        30          3
        20          2


Hope this help..
by the way it works in 9i... 8.1.6 throws error

Regards=0D

Sanjay=0D
-----Original Message-----
From: askdba-bounce@xxxxxxxxxxxxx [mailto:askdba-bounce@xxxxxxxxxxxxx]On
Behalf Of Mudhalvan, Moovarkku
Sent: Tuesday, October 26, 2004 7:34 AM
To: askdba@xxxxxxxxxxxxx
Subject: [askdba] Update using sequences


Dear Friends,
        I have to update the values of one table using Sequence. I know
we can update using sequence and it is working fine using the following
Query.=0D
=0D
        UPDATE TBL_SEQ_TRANS SET KEY_NEW_VALUE=3D
SEQ_TRANSACTION_ID.NEXTVAL <mailto:SEQ_TRANSACTION_ID.NEXTVAL@CAPITAL>=0D
   =0D
        But i have to update in the ascending order for example my table
TBL_SEQ_TRANS have two fields KEY_VALUE and KEY_NEW_VALUE
   =0D
        KEY_VALUE have the values 10,30,20,50,40 . If i use the above
query it is updating like
       =0D
        KEY_VALUE         KEY_NEW_VALUE
            10                            1
            30                            2
            20                            3
            50                            4
            40                            5
=0D
    But i need something like this query UPDATE TBL_SEQ_TRANS SET
KEY_NEW_VALUE=3D SEQ_TRANSACTION_ID.NEXTVAL
<mailto:SEQ_TRANSACTION_ID.NEXTVAL@CAPITAL>  ORDER BY KEY_VALUE;
    so that i will get the output like=0D
=0D
        KEY_VALUE         KEY_NEW_VALUE
            10                            1
            30                            3
            20                            2
            50                            5
            40                            4
     Please let me know if you have any idea.
=0D
Thank You
Mudhalvan M.M
------------------------------------------------------------------------
------------
Global Commercial Real Estate (Japan) Inc.
Moovarkku Mudhalvan, OCP 8i,9i=0D
Database Administator,=0D
E-mail: mmudhalvan@xxxxxxxxxxxxxxx <mailto:mmudhalvan@xxxxxxxxxxxxxxx>=0D
=0D



Confidentiality Notice=0D

The information contained in this electronic message and any attachments to=
 this message are intended
for the exclusive use of the addressee(s) and may contain confidential or=
 privileged information. If
you are not the intended recipient, please notify the sender at Wipro or=
 Mailadmin@xxxxxxxxx immediately
and destroy all copies of this message and any attachments.
[askdba] Re: Update using sequences

Other related posts:
