1 Reply Latest reply: Sep 28, 2013 3:36 AM by Etbin RSS

    Updation of  bulk data

    1043556

      Hi all,

        please give me the solutions for the fallowing scenarios

       

      1.i  want to update a table (having 10 millions of records) of commission based on the salary.if the salary  is <50000 we need to update commission with 10% of salary(i need efficient way).

       

      2. i have a Table it has 100 rec with 3 dept  i need o/p like below

          deptno         enam1       ename2       ename3

            10                smith         king             ohlly

            20                kelly          relly              kin

            -                    -                -                  -

       

      3.i have a table of 2 cols date  and amount

              date               amount

              01-aug-13         1000

             02-aug-13           2000

             03-aug-13          5000

      i need output like below(amount should be added to the previous amount)

             date               amount          result

             01-aug-13       1000              1000

             02-aug-13        2000              3000

            03-aug-13        5000               8000

        • 1. Re: Updation of  bulk data
          Etbin

          3. the easy one first SUM

           

          with

          the_data as

          (select to_date('01-aug-13','dd-mon-rr') the_date,1000 amount from dual union all

          select to_date('02-aug-13','dd-mon-rr'),2000 from dual union all

          select to_date('03-aug-13','dd-mon-rr'),5000 from dual

          )

          select the_date,amount,sum(amount) over (order by the_date) result

            from the_data

           

          THE_DATEAMOUNTRESULT
          08/01/201310001000
          08/02/201320003000
          08/03/201350008000

           

          2. proceeding bottom up

           

          select deptno,

                 max(case when mod(rn,3) = 1 then ename end) ename1,

                 max(case when mod(rn,3) = 2 then ename end) ename2,

                 max(case when mod(rn,3) = 0 then ename end) ename3

            from (select deptno,ename,row_number() over (partition by deptno order by ename) rn

                    from emp

                 )

          group by deptno,ceil(rn / 3)

          order by deptno,ceil(rn / 3)

           

          DEPTNOENAME1ENAME2ENAME3
          10CLARKKINGMILLER
          20ADAMSFORDJONES
          20SCOTTSMITH-
          30ALLENBLAKEJAMES
          30MARTINTURNERWARD

           

          1. NOT TESTED!


          update the_table

              set commission = 0.1 * salary -- or maybe commission + 0.1 * salary

          where salary < 50000


          Regards

           

          Etbin