?
Solved

Oracle

Posted on 2012-09-14
10
Medium Priority
?
485 Views
Last Modified: 2012-09-17
I want to update a column in my table with the row num of the of the results. For instance, the column name is seq_order and the the rownum is what I want the update to set it too. Here is the select and below that are the values. How can I update seq_order with the rownum? Please help!!! Thanks

 

select rownum, seq_order from omni_ent_page_content where page_id=650020 order by seq_order asc

seq_order   rownum

6                   1

7                   2

13                3

1                   4

8                   5

9                   6

2                   7

10                8

14                9

3                  10

11               11

12               12

4                  13

5                  14
");
0
Comment
Question by:jknj72
  • 6
  • 4
10 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38400295
Easiest way is using MERGE:

drop table tab1 purge;
create table tab1(col1 char(1), seq_num number);

insert into tab1 values('a',null);
insert into tab1 values('b',null);
insert into tab1 values('c',null);
commit;


merge into tab1 t1
using (select col1, row_number() over (order by col1 desc) myrownum from tab1) t2
on (t1.col1 = t2.col1)
when matched then update set t1.seq_num = t2.myrownum
/

select * from tab1;

Open in new window

0
 

Author Comment

by:jknj72
ID: 38405303
I tried to run this update but either Im doing something wrong or there is an issue with the merge. I think Im screwing it up but please let me know where....Thanks

merge into omni_ent_page_content_test t1
using (select section_ordinal, row_number() over (order by section_ordinal desc) myrownum from omni_ent_page_content_test) t2
on (t1.section_ordinal = t2.section_ordinal)
when matched then update set t1.section_ordinal = t2.myrownum

Error:
Columns referenced in the ON Clause cannot be updated "T1"."Sectional_Ordinal"
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38405446
>>Columns referenced in the ON Clause cannot be updated "T1"."Sectional_Ordinal"

Error is self-explainitory:  You cannot update the column used in the merge for the join.

Also, the update you are trying to make doesn't make sense.

If you order by section_ordinal and update it based on that order, won't you bascially be updating them to the same values (or reversed order since you set the order to descending)?

In other words:
The order by on section_ordinal will cause them to be in a defined order.  What are you wanting to change the order to?

Please post more about your requirements.  There has to be other columns involved that define the section_ordinal order.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:jknj72
ID: 38405697
I was hoping that I cuold update the Sectional_Ordinal to be the rownumber based on the order by and not the physical rown number. Is this possible? Thanks
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38405723
>>update the Sectional_Ordinal to be the rownumber based on the order by and not the physical rown number

But you are ordering by Sectional_Ordinal.  What are you wanting to change the order to?

Using the first few rows of your original post:

seq_order   rownum
6                   1
7                   2
13                3
1                   4

Why does seq_order 6 become the 1 and 1 become 4th?  This has to be based on some other column.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38405735
Let's forget about rownum for a minute.  Please post sample data and expected results.
0
 

Author Comment

by:jknj72
ID: 38405772
yes Im not sure rownumber is what im looking for now. Heres what Im trying to do, If I have seq_order from 1 to 5 and I get rid of 3, I want 4 to become 3 and 5 to become 4. In other words after a delete I have to reorder the columns.
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 38405833
Why reorder them?  Just use the row_number function to produce sequential numbers.

Since it appears that there are no other columns used in the ordering, you can update them using the rowid and merge statement.

Both examples are below.

If neither of these work, please provide sample data loser to what you need.  I'm still thinking there is a reason behind the sequence numbers that you are not providing.

drop table tab1 purge;
create table tab1( seq_num number);
insert into tab1 values(1);
insert into tab1 values(2);
insert into tab1 values(3);
insert into tab1 values(4);
commit;


--row_number to produce the gapless numbers
select seq_num, row_number() over(order by seq_num) my_seq_num from tab1;
delete from tab1 where seq_num=3;
commit;
select seq_num, row_number() over(order by seq_num) my_seq_num from tab1;



merge into tab1 t1
using (select rowid myrowid, row_number() over (order by seq_num desc) myrownum from tab1) t2
on (t1.rowid = t2.myrowid)
when matched then update set t1.seq_num = t2.myrownum
/

Open in new window

0
 

Author Comment

by:jknj72
ID: 38405950
ok I think that was the answer I needed. I need to have a where clause so I can pass the id though. Im just not sure where I put that in? Can you let me know where I would put that and the points are yours. Thanks for your help!!
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38406005
>>I need to have a where clause so I can pass the id though. Im just not sure where I put that in?

I don't understand.  This is why I'm asking for sample data and expected results.
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question