## [askdba] Re: Update using sequences

• From: "Mudhalvan, Moovarkku" <mmudhalvan@xxxxxxxxxxxxxxx>
• Date: Tue, 26 Oct 2004 13:52:08 +0900

```Great yeah!!! It is working perfectly for me.

Thank You
Mudhalvan M.M

-----Original Message-----
On Behalf Of sanjay.khangarot@xxxxxxxxx
Sent: Tuesday, October 26, 2004 12:54 PM
Subject: [askdba] Re: Update using sequences

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.

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=3D0D

blck>update (select x,y from temp order by x)
2  set y=3D3Dseq_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=3D0D

Sanjay=3D0D
-----Original Message-----
Behalf Of Mudhalvan, Moovarkku
Sent: Tuesday, October 26, 2004 7:34 AM

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.=3D0D =3D0D
UPDATE TBL_SEQ_TRANS SET KEY_NEW_VALUE=3D3D
SEQ_TRANSACTION_ID.NEXTVAL
<mailto:SEQ_TRANSACTION_ID.NEXTVAL@CAPITAL>=3D0D
=3D0D
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
=3D0D
KEY_VALUE have the values 10,30,20,50,40 . If i use the above
query it is updating like
=3D0D
KEY_VALUE         KEY_NEW_VALUE
10                            1
30                            2
20                            3
50                            4
40                            5
=3D0D
But i need something like this query UPDATE TBL_SEQ_TRANS SET
KEY_NEW_VALUE=3D3D SEQ_TRANSACTION_ID.NEXTVAL
<mailto:SEQ_TRANSACTION_ID.NEXTVAL@CAPITAL>  ORDER BY KEY_VALUE;
so that i will get the output like=3D0D
=3D0D
KEY_VALUE         KEY_NEW_VALUE
10                            1
30                            3
20                            2
50                            5
40                            4
Please let me know if you have any idea.
=3D0D
Thank You
Mudhalvan M.M
------------------------------------------------------------------------
------------
Global Commercial Real Estate (Japan) Inc.
Moovarkku Mudhalvan, OCP 8i,9i=3D0D
E-mail: mmudhalvan@xxxxxxxxxxxxxxx
<mailto:mmudhalvan@xxxxxxxxxxxxxxx>=3D0D
=3D0D

Confidentiality Notice=3D0D

The information contained in this electronic message and any attachments
to=3D  this message are intended for the exclusive use of the =