3 Replies Latest reply: Feb 4, 2013 11:03 PM by jeneesh RSS

    One Update Statment

    user13653962
      Hi ,

      I wana use two column conditionally in an update statement rather then writing two statement for each conditions.
        update war_room_report_aged
           set case when p_business_days_old=15 then nrpc_15_days 
                    when p_business_days_old=25 then nrpc_25_days 
                    when p_business_days_old=10 then nrpc_10_days end  = exceeds_sr.c_date_recd
         where bus_unit_abbrev = exceeds_sr.abr_bus_unit||'_'||exceeds_sr.abr_sub
           and todays_date     = p_end_date
           and tio_priority    = p_level;
      I can do it with two statement seperatly ,but i got in a situation where there would be more than 5 columns need to be updated according to parameter's value.
      if p_business_days_old=15 then
       update war_room_report_aged
           set  nrpc_15_days=exceeds_sr.c_date_recd
         where bus_unit_abbrev = exceeds_sr.abr_bus_unit||'_'||exceeds_sr.abr_sub
           and todays_date     = p_end_date
           and tio_priority    = p_level;
      elsif p_business_days_old=25 then
       update war_room_report_aged
           set  nrpc_25_days=exceeds_sr.c_date_recd
         where bus_unit_abbrev = exceeds_sr.abr_bus_unit||'_'||exceeds_sr.abr_sub
           and todays_date     = p_end_date
           and tio_priority    = p_level;
      elsif .. then
       ....
      end if;
      Can i made the same above in one statment?I am so devastated as there gonna be more than 10 conditions for 10 parameter and for each parameter's value relate to seperate 10 cloumns.
        • 1. Re: One Update Statment
          jeneesh
          update war_room_report_aged
               set  nrpc_25_days=
                    case when p_business_days_old=25 
                         then exceeds_sr.c_date_recd
                         else nrpc_25_days
                    end,
                 nrpc_15_days=
                    case when p_business_days_old=15 
                         then exceeds_sr.c_date_recd
                         else nrpc_15_days,
                 nrpc_10_days=
                    case when p_business_days_old=10 
                         then exceeds_sr.c_date_recd
                         else nrpc_10_days
                    end
             where bus_unit_abbrev = exceeds_sr.abr_bus_unit||'_'||exceeds_sr.abr_sub
               and todays_date     = p_end_date
               and tio_priority    = p_level;
          • 2. Re: One Update Statment
            Karthick_Arp
            Like this
            update war_room_report_aged 
               set nrpc_15_days = decode(p_business_days_old, 15, exceeds_sr.c_date_recd, nrpc_15_days)
                 , nrpc_25_days = decode(p_business_days_old, 25, exceeds_sr.c_date_recd, nrpc_25_days)
                 , nrpc_10_days = decode(p_business_days_old, 10, exceeds_sr.c_date_recd, nrpc_10_days)
             where bus_unit_abbrev = exceeds_sr.abr_bus_unit||'_'||exceeds_sr.abr_sub
               and todays_date     = p_end_date
               and tio_priority    = p_level;
            • 3. Re: One Update Statment
              Purvesh K
              You can do it this way:
              create table test_table
              (
               pk_col     number,
               cond_1     number,
               col_1      varchar2(5),
               cond_2     number,
               col_2      varchar2(5),
               cond_3     number,
               col_3      varchar2(5)
              );
              
              insert into test_Table values (1, 10, 'AAA', 20, 'BBB', 30, 'CCC');
              
              update test_table
                 set col_1 = decode(&input, 10, col_1 || '1', col_1),
                     col_2 = decode(&input, 20, col_2 || '1', col_2),
                     col_3 = decode(&input, 30, col_3 || '1', col_3)
               where pk_col = 1;
               
              select *
                from test_table;
              PK_COL                 COND_1                 COL_1 COND_2                 COL_2 COND_3                 COL_3 
              ---------------------- ---------------------- ----- ---------------------- ----- ---------------------- ----- 
              1                      10                     AAA1  20                     BBB   30                     CCC
              Logic is to check if the variable matches a certain Input condition, then update the column with new value, else just update it with Itself. This way, you achieve it in a single SQL and still do not risk losing column information.