Hi Maryann,
I can do this easily when creating a new table using:
create table new_table as
select emp_id,
row_number() over (partition by emp_id order by rec_id) as rec_id
from original_table;
Translating this into an update statement is probably a bit awkward for me
- I'd just create a new table and do some renaming (as long as that's a
viable option for you). If not, hopefully someone will explain how this
could be constructed as a single update statement.
Regards,
Mark.
PS: Otherwise, if you are on Oracle 9 you could create some anonymous
pl/sql using the above statement as a cursor but include the rowid as a
field. Loop through the cursor, updating rec_id where rowid =
cursor.rowid. Unfortunately this won't work easily on Oracle 8 since the
PL/SQL engine doesn't know about analytical functions - you'd have to add
another layer of complexity (probably a temporary table).
Maryann Atkinson
<[EMAIL PROTECTED] To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
.com> cc:
Sent by: Subject: Renumber a set of grupped
rows?
[EMAIL PROTECTED]
.com
21/01/2004 09:24
Please respond to
ORACLE-L
I have a 10000-rows table with 2 columns, Emp_ID and Req_ID.
There are about 150 different emp_ids in these 10000+ records.
What I want to do is the following:
For every different Emp_id, I need the Rec_ids that corresponds to it
to be updated/renumbered starting from 1 and keep going up by 1.
So I want it to look something like this:
Emp_ID Req_ID
10001 001
10001 002
10001 003
10001 004
10001 005
10001 006
10001 007
10001 008
10002 001
10002 002
10002 003
10002 004
10002 005
10003 001
10004 001
10004 002
10004 003
10004 004
10004 005
10004 006
etc
Any ideas?
Thanks,
maa
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Maryann Atkinson
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Privileged/Confidential information may be contained in this message.
If you are not the addressee indicated in this message (or responsible for delivery of
the message to such person), you may not copy or deliver this message to anyone.
In such a case, you should destroy this message and kindly notify the sender by reply
e-mail or by telephone on (03) 9612-6999 or (61) 3 9612-6999.
Please advise immediately if you or your employer does not consent to Internet e-mail
for messages of this kind.
Opinions, conclusions and other information in this message that do not relate to the
official business of Transurban Infrastructure Developments Limited and CityLink
Melbourne Limited shall be understood as neither given nor endorsed by them.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mark Richard
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
