[askdba] Re: Update using sequences

  • From: "Subodh Deshpande" <subodh_deshpande@xxxxxxxxx>
  • To: askdba@xxxxxxxxxxxxx
  • Date: Tue, 26 Oct 2004 01:45:43 -0500

nice one..:) keep posted
----- Original Message -----
From: <sanjay.khangarot@xxxxxxxxx>
To: <askdba@xxxxxxxxxxxxx>
Subject: [askdba] Re: Update using sequences
Date: Tue, 26 Oct 2004 09:23:57 +0530

>=20
>=20
> in 9i you can do in this fashion
> I have created a temporary table and inserted dummy values in it..
>=20
> blck>create table temp (x number,y number);
> Table created.
>=20
> blck>create sequence seq_x start with 1 increment by 1;
> Sequence created.
>=20
> 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)
>=20
> 1 row created.
>=20
> 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)
>=20
> 1 row created.
>=20
> 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)
>=20
> 1 row created.
>=20
> here is the query to update in the order of x=3D0D
>=20
> blck>update (select x,y from temp order by x)
>   2  set y=3D3Dseq_x.nextval;
>=20
> 3 rows updated.
>=20
> blck>select * from temp;
>=20
>          X          Y
> ---------- ----------
>         10          1
>         30          3
>         20          2
>=20
>=20
> Hope this help..
> by the way it works in 9i... 8.1.6 throws error
>=20
> Regards=3D0D
>=20
> 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
>=20
>=20
> 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>=3D=
0D
>    =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>=3D=
0D
> =3D0D
>=20
>=20
>=20
> Confidentiality Notice=3D0D
>=20
> 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.
>=20
>=20



Good Luck..Subodh Deshpande

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Experience Is Knowledge
Wisdom Is Philosophy
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D







--=20
_______________________________________________
Find what you are looking for with the Lycos Yellow Pages
http://r.lycos.com/r/yp_emailfooter/http://yellowpages.lycos.com/default.as=
p?SRC=3Dlycos10


Other related posts: