This discussion is archived
1 2 Previous Next 27 Replies Latest reply: May 9, 2012 3:57 AM by Oracle_Walker RSS

Update a column based on the priority

Oracle_Walker Newbie
Currently Being Moderated
I need to prepare an update statement which should update a column value to '10' based on the priority mentioned. Below is the table structure and sample data with priority logic.
CREATE TABLE TESTING (NAME VARCHAR2(10),VENDOR VARCHAR2(10),TYPE VARCHAR2(20),PRIORITY NUMBER(10));

INSERT INTO TESTING (NAME ,VENDOR ,TYPE ,PRIORITY ) VALUES ('SRC','MS','DOM',null);
INSERT INTO TESTING (NAME ,VENDOR ,TYPE ,PRIORITY ) VALUES ('SRC','XP','ENG',null);
INSERT INTO TESTING (NAME ,VENDOR ,TYPE ,PRIORITY ) VALUES ('SRC','XP','USA',null);
INSERT INTO TESTING (NAME ,VENDOR ,TYPE ,PRIORITY ) VALUES ('SRC','XP','GER',null);
INSERT INTO TESTING (NAME ,VENDOR ,TYPE ,PRIORITY ) VALUES ('SRC','XP','FRA',null);
INSERT INTO TESTING (NAME ,VENDOR ,TYPE ,PRIORITY ) VALUES ('SRC','XP','ITA',null);
PRIORITY LOGIC:
1) MS-> DOM
2) XP-> USA
3) XP-> GER
4) XP-> ITA
5) XP-> FRA
6) XP-> ENG

For above records, the first record which is having vendor as MS and type as DOM needs to be given higher priority and it should get updated with '10' in the priority column.

If vendor MS & type DOM is not available for a name then priority goes to vendor XP with type as USA.

Edited by: Balaji on Apr 25, 2012 4:36 PM
  • 1. Re: Update a column based on the priority
    918949 Pro
    Currently Being Moderated
    drop TABLE TESTING;
    CREATE TABLE TESTING (NAME VARCHAR2(10),VENDOR VARCHAR2(10),TYPE VARCHAR2(20),PRIORITY NUMBER(10));
     
    INSERT INTO TESTING (NAME ,VENDOR ,TYPE ,PRIORITY ) VALUES ('SRC','MS','DOM',null);
    INSERT INTO TESTING (NAME ,VENDOR ,TYPE ,PRIORITY ) VALUES ('SRC','XP','ENG',null);
    INSERT INTO TESTING (NAME ,VENDOR ,TYPE ,PRIORITY ) VALUES ('SRC','XP','USA',null);
    INSERT INTO TESTING (NAME ,VENDOR ,TYPE ,PRIORITY ) VALUES ('SRC','XP','GER',null);
    INSERT INTO TESTING (NAME ,VENDOR ,TYPE ,PRIORITY ) VALUES ('SRC','XP','FRA',null);
    INSERT INTO TESTING (NAME ,VENDOR ,TYPE ,PRIORITY ) VALUES ('SRC','XP','ITA',null);
    INSERT INTO TESTING (NAME ,VENDOR ,TYPE ,PRIORITY ) VALUES ('SRC1','XP','ENG',null);
    INSERT INTO TESTING (NAME ,VENDOR ,TYPE ,PRIORITY ) VALUES ('SRC1','XP','USA',null);
    INSERT INTO TESTING (NAME ,VENDOR ,TYPE ,PRIORITY ) VALUES ('SRC1','XP','GER',null);
    INSERT INTO TESTING (NAME ,VENDOR ,TYPE ,PRIORITY ) VALUES ('SRC1','XP','FRA',null);
    INSERT INTO TESTING (NAME ,VENDOR ,TYPE ,PRIORITY ) VALUES ('SRC1','XP','ITA',null);
    commit;
    
    merge into TESTING tt
    using
            (
            select t.*
            from TESTING t
            where VENDOR = 'MS' and TYPE = 'DOM'
            union all
            select t.*
            from TESTING t
            where VENDOR = 'XP' and TYPE = 'USA' and not exists (
                                                             select 1
                                                             from TESTING t1
                                                             where t1.VENDOR = 'MS' and t1.TYPE = 'DOM' and t1.NAME = t.NAME
                                                             )
            ) ttt
    on ( tt.NAME = ttt.NAME and tt.VENDOR = ttt.VENDOR and tt.TYPE = ttt.TYPE)
    when matched then 
    update set PRIORITY = 10;
    commit;
    
    select * from TESTING;
    
    Table dropped.
    Table created.
    1 row created.
    1 row created.
    1 row created.
    1 row created.
    1 row created.
    1 row created.
    1 row created.
    1 row created.
    1 row created.
    1 row created.
    1 row created.
    Commit complete.
    Merge successfully completed.
    Commit complete.
    
    NAME       VENDOR     TYPE                   PRIORITY
    ---------- ---------- -------------------- ----------
    SRC        MS         DOM                          10
    SRC        XP         ENG                            
    SRC        XP         USA                            
    SRC        XP         GER                            
    SRC        XP         FRA                            
    SRC        XP         ITA                            
    SRC1       XP         ENG                            
    SRC1       XP         USA                          10
    SRC1       XP         GER                            
    SRC1       XP         FRA                            
    SRC1       XP         ITA                            
    
    11 rows selected.
  • 2. Re: Update a column based on the priority
    Onenessboy Newbie
    Currently Being Moderated
    something like this below example
    UPDATE tab1
    SET col2=
      CASE
        WHEN (col1 IN ('1001','1012','50055'))
        THEN 'A'
        WHEN (col1 IN ('5','10','98','0'))
        THEN 'B'
        WHEN (col1 IN ('11','876','7976','67465'))
        THEN 'C'
        else 'Missing' 
        END 
  • 3. Re: Update a column based on the priority
    Oracle_Walker Newbie
    Currently Being Moderated
    Its updating priority for both the rows, but only one row should get updated with priority as 10 for a name.

    Expected O/P for the given sample record is ,
    NAME       VENDOR     TYPE                   PRIORITY
    ---------- ---------- -------------------- ----------
    SRC        MS         DOM                          10
    SRC        XP         ENG                            
    SRC        XP         USA                          
    SRC        XP         GER                            
    SRC        XP         FRA                            
    SRC        XP         ITA                            
  • 4. Re: Update a column based on the priority
    Oracle_Walker Newbie
    Currently Being Moderated
    I am not sure if we can adapt case statement here. Because it will update multiple rows for a single name. Only one record for a name should be updated with priority as 10.
  • 5. Re: Update a column based on the priority
    918949 Pro
    Currently Being Moderated
    edited ...
  • 6. Re: Update a column based on the priority
    Onenessboy Newbie
    Currently Being Moderated
    Igor,

    Excellent solution...thank you :) i got to know something which i didnt try :)
  • 7. Re: Update a column based on the priority
    Oracle_Walker Newbie
    Currently Being Moderated
    Awesome Igor! Thank you for helping out. I will extend this merge statement to include priorities until the final one(XP-> ENG).
  • 8. Re: Update a column based on the priority
    Stew Ashton Expert
    Currently Being Moderated
    Or try this:
    merge into testing o
    using (
    with data as (
        SELECT ROWID RID, NAME,
        CASE WHEN (VENDOR, TYPE) = (('MS', 'DOM')) THEN 1
          WHEN (VENDOR, TYPE) = (('XP', 'USA')) THEN 2
          WHEN (VENDOR, TYPE) = (('XP', 'GER')) THEN 3 
          WHEN (VENDOR, TYPE) = (('XP', 'ITA')) THEN 4 
          WHEN (VENDOR, TYPE) = (('XP', 'FRA')) THEN 5 
          WHEN (VENDOR, TYPE) = (('XP', 'ENG')) THEN 6 
          end precedence
       from testing a
      )
      select rid, 
      case when precedence = min(precedence) over(partition by name) then 10 end priority
      from data
    ) n on (o.rowid = n.rid)
    WHEN MATCHED THEN UPDATE SET PRIORITY = N.PRIORITY
      where decode(o.priority, n.priority,0,1) = 1;
    Notice that if you run it twice in a row, the second time it updates zero rows. This reduces REDO and UNDO.

    DECODE considers two NULLs to be "equal"; this makes equality comparisons easier.

    Edited by: Stew Ashton on Apr 25, 2012 11:23 PM (changed CASE to compare columns individually)
  • 9. Re: Update a column based on the priority
    918949 Pro
    Currently Being Moderated
    drop TABLE TESTING;
    CREATE TABLE TESTING (NAME VARCHAR2(10),VENDOR VARCHAR2(10),TYPE VARCHAR2(20),PRIORITY NUMBER(10));
     
    INSERT INTO TESTING (NAME ,VENDOR ,TYPE ,PRIORITY ) VALUES ('SRC','MS','DOM',null);
    INSERT INTO TESTING (NAME ,VENDOR ,TYPE ,PRIORITY ) VALUES ('SRC','XP','ENG',null);
    INSERT INTO TESTING (NAME ,VENDOR ,TYPE ,PRIORITY ) VALUES ('SRC','XP','USA',null);
    INSERT INTO TESTING (NAME ,VENDOR ,TYPE ,PRIORITY ) VALUES ('SRC','XP','GER',null);
    INSERT INTO TESTING (NAME ,VENDOR ,TYPE ,PRIORITY ) VALUES ('SRC','XP','FRA',null);
    INSERT INTO TESTING (NAME ,VENDOR ,TYPE ,PRIORITY ) VALUES ('SRC','XP','ITA',null);
    INSERT INTO TESTING (NAME ,VENDOR ,TYPE ,PRIORITY ) VALUES ('SRC1','XP','ENG',null);
    INSERT INTO TESTING (NAME ,VENDOR ,TYPE ,PRIORITY ) VALUES ('SRC1','XP','USA',null);
    INSERT INTO TESTING (NAME ,VENDOR ,TYPE ,PRIORITY ) VALUES ('SRC1','XP','GER',null);
    INSERT INTO TESTING (NAME ,VENDOR ,TYPE ,PRIORITY ) VALUES ('SRC1','XP','FRA',null);
    INSERT INTO TESTING (NAME ,VENDOR ,TYPE ,PRIORITY ) VALUES ('SRC1','XP','ITA',null);
    commit;
    
    merge into TESTING tt
    using
            (
            select t.*, case when decode(vendor||'$'||type, 'MS$DOM', 1, 'XP$USA', 2, 'XP$ENG', 3, 100) = 
                                                min(decode(vendor||'$'||type, 'MS$DOM', 1, 'XP$USA', 2, 'XP$ENG', 3, 100)) over (partition by NAME)
                                   then 10
                                   else null
                                   end PRIORITY_
            from TESTING t
            ) ttt
    on ( tt.NAME = ttt.NAME and tt.VENDOR = ttt.VENDOR and tt.TYPE = ttt.TYPE)
    when matched then 
    update set tt.PRIORITY = ttt.PRIORITY_;
    commit;
    
    select * from TESTING;
    
    Table dropped.
    Table created.
    1 row created.
    1 row created.
    1 row created.
    1 row created.
    1 row created.
    1 row created.
    1 row created.
    1 row created.
    1 row created.
    1 row created.
    1 row created.
    Commit complete.
    Merge successfully completed.
    Commit complete.
    
    NAME       VENDOR     TYPE                   PRIORITY
    ---------- ---------- -------------------- ----------
    SRC        MS         DOM                          10
    SRC        XP         ENG                            
    SRC        XP         USA                            
    SRC        XP         GER                            
    SRC        XP         FRA                            
    SRC        XP         ITA                            
    SRC1       XP         ENG                            
    SRC1       XP         USA                          10
    SRC1       XP         GER                            
    SRC1       XP         FRA                            
    SRC1       XP         ITA                            
    
    11 rows selected.
  • 10. Re: Update a column based on the priority
    Oracle_Walker Newbie
    Currently Being Moderated
    Thank you Stew. Learning a lot here. I am getting hard to understand your code. Complex!
  • 11. Re: Update a column based on the priority
    Stew Ashton Expert
    Currently Being Moderated
    Run it once, it merges 11 rows.

    Run it again, it merges 11 rows. Why change rows that are already good?
  • 12. Re: Update a column based on the priority
    918949 Pro
    Currently Being Moderated
    Why change rows that are already good?
    if you have new data in table, then you can make reload of priority.
    set serveroutput on;
    begin
        merge into TESTING tt
        using
                (
                        select t.*, case when decode(vendor||'$'||type, 'MS$DOM', 1, 'XP$USA', 2, 'XP$ENG', 3, 100) = 
                                                            min(decode(vendor||'$'||type, 'MS$DOM', 1, 'XP$USA', 2, 'XP$ENG', 3, 100)) over (partition by NAME)
                                               then 10
                                               else null
                                               end PRIORITY_
                        from TESTING t
                ) ttt
        on ( tt.NAME = ttt.NAME and tt.VENDOR = ttt.VENDOR and tt.TYPE = ttt.TYPE and PRIORITY_ is not null)
        when matched then 
        update set tt.PRIORITY = ttt.PRIORITY_;
        dbms_output.put_line('Merge rows: '||sql%rowcount);
         commit;
    
    end;
    /
    
    Merge rows: 2
    PL/SQL procedure successfully completed.
  • 13. Re: Update a column based on the priority
    Stew Ashton Expert
    Currently Being Moderated
    Yes, but reload the priority only when it changes. If the table is huge, that could make a big difference in REDO and UNDO.
  • 14. Re: Update a column based on the priority
    918949 Pro
    Currently Being Moderated
    drop TABLE TESTING;
    CREATE TABLE TESTING (NAME VARCHAR2(10),VENDOR VARCHAR2(10),TYPE VARCHAR2(20),PRIORITY NUMBER(10));
     
    INSERT INTO TESTING (NAME ,VENDOR ,TYPE ,PRIORITY ) VALUES ('SRC','MS','DOM',null);
    INSERT INTO TESTING (NAME ,VENDOR ,TYPE ,PRIORITY ) VALUES ('SRC','XP','ENG',null);
    INSERT INTO TESTING (NAME ,VENDOR ,TYPE ,PRIORITY ) VALUES ('SRC','XP','USA',null);
    INSERT INTO TESTING (NAME ,VENDOR ,TYPE ,PRIORITY ) VALUES ('SRC','XP','GER',null);
    INSERT INTO TESTING (NAME ,VENDOR ,TYPE ,PRIORITY ) VALUES ('SRC','XP','FRA',null);
    INSERT INTO TESTING (NAME ,VENDOR ,TYPE ,PRIORITY ) VALUES ('SRC','XP','ITA',null);
    INSERT INTO TESTING (NAME ,VENDOR ,TYPE ,PRIORITY ) VALUES ('SRC1','XP','ENG',null);
    INSERT INTO TESTING (NAME ,VENDOR ,TYPE ,PRIORITY ) VALUES ('SRC1','XP','USA',null);
    INSERT INTO TESTING (NAME ,VENDOR ,TYPE ,PRIORITY ) VALUES ('SRC1','XP','GER',null);
    INSERT INTO TESTING (NAME ,VENDOR ,TYPE ,PRIORITY ) VALUES ('SRC1','XP','FRA',null);
    INSERT INTO TESTING (NAME ,VENDOR ,TYPE ,PRIORITY ) VALUES ('SRC1','XP','ITA',null);
    commit;
    
    set serveroutput on;
    begin
        merge into TESTING tt
        using
                (
                        select t.*, case when decode(vendor||'$'||type, 'MS$DOM', 1, 'XP$USA', 2, 'XP$ENG', 3, 100) = 
                                                            min(decode(vendor||'$'||type, 'MS$DOM', 1, 'XP$USA', 2, 'XP$ENG', 3, 100)) over (partition by NAME)
                                               then 10
                                               else null
                                               end PRIORITY_
                        from TESTING t
                ) ttt
        on ( tt.NAME = ttt.NAME and tt.VENDOR = ttt.VENDOR and tt.TYPE = ttt.TYPE)
        when matched then 
        update set tt.PRIORITY = ttt.PRIORITY_
                where (tt.PRIORITY is null and ttt.PRIORITY_ is not null) 
                        or (tt.PRIORITY is not null and ttt.PRIORITY_ is null) ;
        dbms_output.put_line('Merge rows: '||sql%rowcount);
         commit;
     
    end;
    / 
    
    select * from TESTING;
    
    INSERT INTO TESTING (NAME ,VENDOR ,TYPE ,PRIORITY ) VALUES ('SRC1','MS','DOM',null);
    commit;
    
    set serveroutput on;
    begin
        merge into TESTING tt
        using
                (
                        select t.*, case when decode(vendor||'$'||type, 'MS$DOM', 1, 'XP$USA', 2, 'XP$ENG', 3, 100) = 
                                                            min(decode(vendor||'$'||type, 'MS$DOM', 1, 'XP$USA', 2, 'XP$ENG', 3, 100)) over (partition by NAME)
                                               then 10
                                               else null
                                               end PRIORITY_
                        from TESTING t
                ) ttt
        on ( tt.NAME = ttt.NAME and tt.VENDOR = ttt.VENDOR and tt.TYPE = ttt.TYPE)
        when matched then 
        update set tt.PRIORITY = ttt.PRIORITY_
                where (tt.PRIORITY is null and ttt.PRIORITY_ is not null) 
                        or (tt.PRIORITY is not null and ttt.PRIORITY_ is null) ;
        dbms_output.put_line('Merge rows: '||sql%rowcount);
         commit;
     
    end;
    / 
    
    select * from TESTING;
    
    Table dropped.
    Table created.
    1 row created.
    1 row created.
    1 row created.
    1 row created.
    1 row created.
    1 row created.
    1 row created.
    1 row created.
    1 row created.
    1 row created.
    1 row created.
    Commit complete.
    Merge rows: 2
    PL/SQL procedure successfully completed.
    
    NAME       VENDOR     TYPE                   PRIORITY
    ---------- ---------- -------------------- ----------
    SRC        MS         DOM                          10
    SRC        XP         ENG                            
    SRC        XP         USA                            
    SRC        XP         GER                            
    SRC        XP         FRA                            
    SRC        XP         ITA                            
    SRC1       XP         ENG                            
    SRC1       XP         USA                          10
    SRC1       XP         GER                            
    SRC1       XP         FRA                            
    SRC1       XP         ITA                            
    
    11 rows selected.
    1 row created.
    Commit complete.
    Merge rows: 2
    PL/SQL procedure successfully completed.
    
    NAME       VENDOR     TYPE                   PRIORITY
    ---------- ---------- -------------------- ----------
    SRC        MS         DOM                          10
    SRC        XP         ENG                            
    SRC        XP         USA                            
    SRC        XP         GER                            
    SRC        XP         FRA                            
    SRC        XP         ITA                            
    SRC1       XP         ENG                            
    SRC1       XP         USA                            
    SRC1       XP         GER                            
    SRC1       XP         FRA                            
    SRC1       XP         ITA                            
    SRC1       MS         DOM                          10
    
    12 rows selected.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points