1 Reply Latest reply on Sep 28, 2013 8:36 AM by Etbin

# Updation of  bulk data

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

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
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