update first 3 rows from each deptno

[aph]

Hi All,

Consider EMP table. I need to update sal = sal+100 of first 3 employee from each DEPTNO of order by deptno, ename. I mean update should perform after order by deptno, ename of first 3 rows. see below.

Before Update
=============
DEPTNO ENAME SAL
--------- ---------- ---------
20 SMITH 800
30 ALLEN 1600
30 WARD 1250
20 JONES 2975
30 MARTIN 1250
30 BLAKE 2850
10 CLARK 2450
20 SCOTT 3000
10 KING 5000
30 TURNER 1500
20 ADAMS 1100
30 JAMES 950
20 FORD 3000
10 MILLER 1300

After Update
============
DEPTNO ENAME SAL
--------- ---------- ---------
10 CLARK 2450+100
10 KING 5000+100
10 MILLER 1300+100
20 ADAMS 1100+100
20 FORD 3000+100
20 JONES 2975+100
20 SCOTT 3000
20 SMITH 800
30 ALLEN 1600+100
30 BLAKE 2850+100
30 JAMES 950+100
30 MARTIN 1250
30 TURNER 1500
30 WARD 1250


Thanks in advance.

[Nighthawki]

You can achieve that using rownum pseudo column.
First use cursor/loop on the depts, and foreach dept, update sal= sal + 100 where rownum <= 3.

PLSQL should be used of course (loops, explicit cursors).

[Nighthawki]

P.S: makes sure you order the results properly after recieving the dept list, otherwise the rownum will be ineffective.

[akkerend]

update emp e
set e.sal = e.sal+100
where e.empno in (select s.empno
from (select e2.empno
, row_number() over (partition by e2.deptno order by e2.ename) as rownumber
from emp e2
) s
where s.rownumber <=3
)

[aph]

Hi Akkerend,

Thanks it work perfect!, thanks for your time to resolve this problem.

[aph]

Hi Akkerend,

Is there any way without using "row_number() over (partition by " we can resolve this, the reason I have to use this query in Developer 2000 Beforereport and can't compile however it works on sql*plus session.

Thanks.

[slimdave]

It ought to run OK as dynamic SQL.

[aph]

"It ought to run OK as dynamic SQL."

Not sure what is your mean.

Thanks.

[slimdave]

Use DBMS_SQL or "execute immediate" syntax to execute the statement

[akkerend]

In 8i some new SQL features, like ROW_NUMBER and other analytical functions, don't work in PL/SQL.

There are two workarounds:
1. Dynamic SQL, with EXECUTE IMMEDIATE:


create procedure...
...
begin
...
execute immediate 'update emp set ...';
...
end;
/


2. create a view and use the view in PL/SQL:


create or replace view emp_rownumber
as
select e.empno
, row_number() over (partition by e.deptno order by e.ename) as rownumber
from emp e;

create procedure...
...
begin
...
update emp e
set e.sal = e.sal+100
where e.empno in (select r.empno
from emp_rownumber r
where r.rownumber <=3
);
...
end;
/

[aph]

Hi Akkerend,
The below is Beforereport of developer 2000, but when I compile I get error.



function BeforeReport return boolean is
begin
EXECUTE IMMEDIATE 'update emp e
set e.sal = e.sal+100
where e.empno in (select s.empno
from (select e2.empno,
row_number() over
(partition by e2.deptno order by e2.ename)
as rownumber
from emp e2) s
where s.rownumber <=3)';
return (TRUE);
end;


Thanks in advance.

[akkerend]

The PL/SQL version in Developer 2000 is an old version of PL/SQL, probably too old for execute immediate. Maybe you can try the procedure DBMS_UTILITY.EXEC_DDL_STATEMENT to execute the update statement. Or use a view.

[aph]

Hi Akkerend,

Thanks for your help and provide solution. I will work on this over the weekend and will update you.

Appreciate.