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

    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.
                • 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 person found this helpful
                                  1 2 Previous Next