[askdba] Re: Update using sequences
- From: "Mudhalvan, Moovarkku" <mmudhalvan@xxxxxxxxxxxxxxx>
- To: <askdba@xxxxxxxxxxxxx>
- Date: Tue, 26 Oct 2004 13:52:08 +0900
Great yeah!!! It is working perfectly for me.
Thank You
Mudhalvan M.M
-----Original Message-----
From: askdba-bounce@xxxxxxxxxxxxx [mailto:askdba-bounce@xxxxxxxxxxxxx]
On Behalf Of sanjay.khangarot@xxxxxxxxx
Sent: Tuesday, October 26, 2004 12:54 PM
To: askdba@xxxxxxxxxxxxx
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.
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=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-----
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.=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
Database Administator,=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 =
addressee(s)
and may contain confidential or=3D privileged information. If you are =
not
the intended recipient, please notify the sender at Wipro or=3D
Mailadmin@xxxxxxxxx immediately and destroy all copies of this message
and any attachments.
Other related posts:
