• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 571
  • Last Modified:

Oracle update merge issue

I asked a question the opther day and I am trying to incorporate this into a cursor and for some reason it is not working. Heres what I need to happen. I have the following records in my db table.
                                                              sequence_num
650021      2082      HEADER                      1
650021      50055      PARAGRAPH      2
650021      1054      LINE                      3
650021      50056      PARAGRAPH      4

This is actually a webpage with different sections you can add and delete lines, paragraphs, images, etcc... The problem Im running into is when I delete, for instance, number 3 from this table I have an update that will realign the sequence(last column). So if I take out 3 it will keep 1 and 2 the same and make 4 to 3, thats it....These get very lengthy so I must make sure this is correct and what I currently have reorders the whole thing, like making 1 to 4, which makes my header go all the way to the bottom so something is off. Anyway, here is the Merge I got the other day hopefully it makes sense to someone and you can help me. Thanks

Passing in PageID(first column)
merge into omni_ent_page_content t1
using (select rowid myrowid, row_number() over (order by section_ordinal desc) myrownum from omni_ent_page_content where page_id = iPAGEID) t2
on (t1.rowid = t2.myrowid)
when matched then update set t1.section_ordinal = t2.myrownum
where page_id = iPAGEID;
0
jknj72
Asked:
jknj72
  • 4
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
How do you insert a new row now?

Say I want a new 'PARAGRAPH'.  Would that need inserted before the LINE above?
0
 
jknj72Author Commented:
I can insert a paragraph anywhere with our front end. We are building our intranet and we are allowing end users to build there own pages. You can insert anything anywhere you want but I need to keep the existing alignment in tact but after a delete I have to realign what I have. Dont pay too much attention to what is being put where, because it doesnt matter, I just need the numbers to be correct when I do the realign. So, if I deleted 3 then 1 and 2 would still be the same and then I would have to adjust the rest. Does this make sense?
0
 
slightwv (䄆 Netminder) Commented:
>>Does this make sense?

Not really.  If the app takes care of the sequence number as far as inserting new rows, why can it not take care of the numbers on delete of a row?

But since it is what you want, I'll see if I can come up with the SQL for it.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
slightwv (䄆 Netminder) Commented:
I think the problem you have is the DESC in the row_number in the code above.

If you take that out, does it not work for you?

merge into omni_ent_page_content t1
using (select rowid myrowid, row_number() over (order by section_ordinal) myrownum from omni_ent_page_content where page_id = iPAGEID) t2
on (t1.rowid = t2.myrowid)
when matched then update set t1.section_ordinal = t2.myrownum
where page_id = iPAGEID;
0
 
slightwv (䄆 Netminder) Commented:
Here is the test case I set up.  If it doesn't work please add to the test case to show where it breaks

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

insert into tab1 values('a',1);
insert into tab1 values('x',2);
insert into tab1 values('b',3);
insert into tab1 values('z',4);
delete from tab1 where seq_num = 3;
commit;

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

select * from tab1 order by 2;
rollback;

Open in new window

0
 
jknj72Author Commented:
that was it..thx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now