9 Replies Latest reply: Jun 21, 2013 4:02 AM by Etbin RSS

    Need a help to update multiple columns within the range from other table

    52aa80c5-cdb3-4213-88a2-f858ad605dfa

      Hi,


      Can anyone advice me a nice approach for the below requirement.

       

      The table TEMP will give be

      CREATE TABLE TEMP

      (

        PK1     VARCHAR2(50 CHAR)                 NOT NULL,

        PK2     VARCHAR2(50 CHAR)                 NOT NULL,

        PK3     VARCHAR2(50 CHAR)                 NOT NULL,

        STARTDATE   DATE                              NOT NULL,

        PERIOD     FLOAT(126),

        NUMOFWEEKS  FLOAT(126)

      );

      The table Main will be (totally 109 columns)

       

      CREATE TABLE Main

      (

        PK1   VARCHAR2(50 CHAR)                 NOT NULL,

        PK2   VARCHAR2(50 CHAR)                 NOT NULL,

        PK3   VARCHAR2(50 CHAR)                 NOT NULL,

        STARTDATE   DATE                              NOT NULL,

        PERIOD1     FLOAT(126),

        PERIOD2     FLOAT(126),

        PERIOD3     FLOAT(126),

        PERIOD4     FLOAT(126),

        PERIOD5     FLOAT(126),

        PERIOD6     FLOAT(126),

      (Till)

        PERIOD102   FLOAT(126),

        PERIOD103   FLOAT(126),

        PERIOD104   FLOAT(126)

      );

       

      I need to updates records in the Main table Starting from the column (PERIODX) from the number given in the PERIOD.TEMP till the number given in the NUMOFWEEKS.TEMP column, selecting only those columns in the Main table whose value = 0 and update the value as 5.

       

      Example

      TEMP table Period as 38 and Noofweeks as 104 so in Main table it will update only between Period 38 to Period104

       

       

               PERIOD1 ------------ PERIOD37                    PERIOD38-------PERIOD 46 -------PERIOD55----------PERIOD104

                      10                          0                                       0             0                         100                      0

       

      In this example it needs to update PERIOD38,PERIOD46 and PERIOD104 as 5.

       

       

      -----------------------------------------------------------------------------------------------------------------------------------------------------------

       

      I tried to write like below but I think this approach will be very poor in case of performance

       

       

      DECLARE

         CURSOR cur_u_validation

         IS

            SELECT pk1, pk2, pk3, period1 i, numofweeks k

              FROM temp;

         

      BEGIN

       

         FOR x IN cur_u_validation

         LOOP

            for j in x.i.. x.k

             loop

            execute immediate 'Update histwide set period||to_char('||j||')=decode(PERIOD||TO_CHAR('||j||'),0,5,PERIOD||TO_CHAR('||j||'))

            where period||to_char('||j||') <> 0 and dmdunit=x.dmdunit and dmdgroup=x.dmdgroup and loc=x.loc' ;

           end loop;

       

         END LOOP;

         COMMIT;

      END;

       

       

      Can anyone suggest any other approach Please

        • 1. Re: Need a help to update multiple columns within the range from other table
          Purvesh K


          Hi,


          Can anyone advice me a nice approach for the below requirement.

           

          The table Main will be (totally 109 columns)

           

          CREATE TABLE Main

          (

            PK1   VARCHAR2(50 CHAR)                 NOT NULL,

            PK2   VARCHAR2(50 CHAR)                 NOT NULL,

            PK3   VARCHAR2(50 CHAR)                 NOT NULL,

            STARTDATE   DATE                              NOT NULL,

            PERIOD1     FLOAT(126),

            PERIOD2     FLOAT(126),

            PERIOD3     FLOAT(126),

            PERIOD4     FLOAT(126),

            PERIOD5     FLOAT(126),

            PERIOD6     FLOAT(126),

          (Till)

            PERIOD102   FLOAT(126),

            PERIOD103   FLOAT(126),

            PERIOD104   FLOAT(126)

          );


          Can anyone suggest any other approach Please

           

          Better and probably the best approach is to take a step back, think and fix the Data model. You are creating 104 columns to store period. Why not just

          CREATE TABLE Main
          (
            PK1   VARCHAR2(50 CHAR)                 NOT NULL,
            PK2   VARCHAR2(50 CHAR)                 NOT NULL,
            PK3   VARCHAR2(50 CHAR)                 NOT NULL,
            STARTDATE   DATE                              NOT NULL,
            PERIOD_reference     varchar2(9)/number(3),
            PERIOD_Value     FLOAT(126)
          );
          
          

           

          Then, create one Row for each of the Period Reference you want and Primary key comprises of PK1, PK2, PK3 and Period Reference.

           

          So, your original problem can now be solved in Two Approaches:

          1. If you have data for all the 104 periods into your table i.e. 104 rows for a particular primary Key config, then Simply update the rows that have Period Reference between your Lower and Upper Bounds.

          2. If you do not have data for entire period, then Insert the Data into table for your Lower and Upper bounds.

           

          You have to choose what approach to follow, but this approach is a lot better than to continue with your wrong Data model. Since, it forces you to make use of Dynamic SQL which will degrade your performance and expose your system for SQL Injection.

          • 2. Re: Need a help to update multiple columns within the range from other table
            _Karthick_

            Try using Static SQL using MERGE statement.

             

            Something like this

             

            merge into main m

            using

            (

               select pk1

               ,      pk2

               ,      pk3

               ,      period

               ,      numofweeks

               from   temp

            ) t

            on

            (

               m.pk1 = t.pk1 and

               m.pk2 = t.pk2 and

               m.pk3 = t.pk3

            )

            when matched then

            update

            set period1   = case when 1 between t.period and t.numofweeks then

                               decode(period1, 0, 5, period1)

                            end

            ,   period2   = case when 2 between t.period and t.numofweeks then

                               decode(period2, 0, 5, period2)

                            end

            ,   ......

            ,   period104 = case when 104 between t.period and t.numofweeks then

                               decode(period104, 0, 5, period104)

                            end;

             

            Fixed the t.numof weeks to t.numofweeks as mentioned by Etbin

            Message was edited by: Karthick_Arp

            • 3. Re: Need a help to update multiple columns within the range from other table
              Etbin

              t.numof weeks should be t.numofweeks (a typo for sure)


              except for the formatting I wanted to propose the same solution, but I checked before posting this time.


              Regards


              Etbin

              • 4. Re: Need a help to update multiple columns within the range from other table
                52aa80c5-cdb3-4213-88a2-f858ad605dfa

                Thanks a lot Karthik... I will try in your way and get back to you.... Again thanks a ton Btw, I asked just approach but you gave me the code .. really gratefull

                • 6. Re: Need a help to update multiple columns within the range from other table
                  Etbin

                  PurveshK wrote:

                  ... Since, it forces you to make use of Dynamic SQL which will degrade your performance and expose your system for SQL Injection.

                  Karthick_Arp doesn't need Dynamic SQL

                   

                  Regards

                   

                  Etbin

                  • 7. Re: Need a help to update multiple columns within the range from other table
                    Purvesh K

                    Etbin wrote:

                     

                    PurveshK wrote:

                    ... Since, it forces you to make use of Dynamic SQL which will degrade your performance and expose your system for SQL Injection.

                    Karthick_Arp doesn't need Dynamic SQL

                     

                    Regards

                     

                    Etbin

                     

                    Correct. But the Static SQL's will then become cumbersome because of 104 period columns.

                    Imagine the number of SQL's to be written or the length of Update with 104 columns as opposed to a small Sql that fetches the record for a particular week.

                     

                    It may be correct to have 104 different columns in a single table, but given the knowledge OP provided, it does not look correct to me and hence suggested the approach to change the data model.

                    • 8. Re: Need a help to update multiple columns within the range from other table
                      Etbin

                      My comment was just there is no need for Dynamic SQL.

                      Kathick_Arp's merge (cumbersome or not) even when updating all columns is IMHO by far superior to any other solution I can think of.

                      Cumbersome: we (humans) often forget that for processors there's no big difference doing 2 * 3 or 222222222222222 * 333333333333333 or ...

                      For the rest (questioning the data model) your suggestion to abandon spreadsheet logic looks more than appropriate to me.

                       

                      Regards

                       

                      Etbin