0

Given the following oracle database table:

group   revision    comment
1       1           1               
1       2           2 
1       null        null 
2       1           1               
2       2           2 
2       3           3 
2       4           4 
2       null        null 
3       1           1               
3       2           2 
3       3           3 
3       null        null

I want to shift the comment column one step down in relation to version, within its group, so that I get the following table: 

group   revision    comment
1       1           null            
1       2           1   
1       null        2  
2       1           null            
2       2           1   
2       3           2 
2       4           3 
2       null        4  
3       1           null            
3       2           1   
3       3           2 
3       null        3

I have the following query:

              

    MERGE INTO example_table t1
    USING example_table t2
    ON (
       (t1.revision = t2.revision+1 OR 
       (t2.revision = (
          SELECT MAX(t3.revision) 
          FROM example_table t3 
          WHERE t3.group = t1.group
       ) AND t1.revision IS NULL)
    ) 
    AND t1.group = t2.group)
    WHEN MATCHED THEN UPDATE SET t1.comment = t2.comment;

That does most of this (still need a separate query to cover revision = 1), but it is very slow.

So my question is, how do I use Max here as efficiently as possible to pull out the highest revision for each group?

Sam Kolier
  • 13
  • 2

1 Answers1

1

I would use lag not max

create table example_table(group_id number, revision number, comments varchar2(40));
insert into example_table values (1,1,1);
insert into example_table values (1,2,2);
insert into example_table values (1,3,null);
insert into example_table values (2,1,1);
insert into example_table values (2,2,2);
insert into example_table values (2,3,3);
insert into example_table values (2,4,null);

select * from example_table;

merge into example_table e
using (select group_id, revision, comments, lag(comments, 1) over (partition by group_id order by revision nulls last) comments1 from example_table) u
on (u.group_id = e.group_id and nvl(u.revision,0) = nvl(e.revision,0))
when matched then update set comments = u.comments1;

select * from example_table;
Kacper
  • 4,798
  • 2
  • 19
  • 34
  • 1
    Yeah this seems to work. So this answer coupled with this http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions001.htm#SQLRF06174 (the section on analytic functions) helped me out a lot. – Sam Kolier Dec 09 '16 at 13:23
  • The UPDATE statement will not work - the WITH clause cannot precede UPDATE, it needs to come after it. And the use of NVL in the ON condition in MERGE is risky - what if 0 is one of the legitimate values of revision? Better to write "or u.revision is null and v.revision is null". (Actually even better would be if the table had a primary key!) Otherwise this is the right answer, good job! –  Dec 09 '16 at 13:44
  • @mathguy of course it won't. It was just my mess when building the answer. Proper answer is merge not the update. – Kacper Dec 09 '16 at 13:50
  • Right - MERGE is the right answer in this case anyway. –  Dec 09 '16 at 13:54