1 2 Previous Next 27 Replies Latest reply: May 9, 2012 5:57 AM by Oracle_Walker RSS

    Update a column based on the priority

    Oracle_Walker
      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
          Igor.M
          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
            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
              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
                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
                  Igor.M
                  edited ...
                  • 6. Re: Update a column based on the priority
                    Onenessboy
                    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
                      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
                        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
                          Igor.M
                          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
                            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
                              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
                                Igor.M
                                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
                                  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
                                    Igor.M
                                    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