update salary for lowest paid employee in each dept [message #421988] |
Sat, 12 September 2009 15:26  |
shrinika
Messages: 306 Registered: April 2008
|
Senior Member |
|
|
Hello,
I have emp and dept table in scott schema. I wanted to update 500$ salary raise for
lowest paid employee in each department.
I have three department number 10,20,30.
Here is the table structure.
SQL> desc emp
Name Null? Type
----------------------- -------- ----------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL>
SQL> desc dept
Name Null? Type
----------------------- -------- ----------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
SQL>
Here is my update statement..
update emp a set sal = sal + 500 where
sal = (select min(sal) from emp b where b.deptno = a.deptno)
and deptno = (select deptno from dept c where c.deptno = a.deptno);
Before update, here is the data.
SQL> select deptno,min(sal) from emp group by deptno;
DEPTNO MIN(SAL)
---------- ----------
10 1300
20 800
30 950
SQL>
SQL> update emp a set sal = sal + 500 where
2 sal = (select min(sal) from emp b where b.deptno = a.deptno)
3 and deptno = (select deptno from dept c where c.deptno = a.deptno);
3 rows updated.
The below output is not showing 500$ raise... Am i missing anything in my update statement?
SQL> select deptno,min(sal) from emp group by deptno;
DEPTNO MIN(SAL)
---------- ----------
10 1800
20 1100
30 1250
SQL>
|
|
|
|
|
Re: update salary for lowest paid employee in each dept [message #422018 is a reply to message #421989] |
Sun, 13 September 2009 09:05   |
jacksilva
Messages: 1 Registered: September 2009
|
Junior Member |
|
|
ThomasG wrote on Sat, 12 September 2009 16:03 | Logical problem, I guess.
For example, when you update the salary for the min(sal) in group 20 from 800 to 1300, then someone else with a salary of 1100 is the new min(sal) when you run the query again.
|
Rightly said its a logical problem. Check the data for minimum as it will change with updates.
|
|
|
Re: update salary for lowest paid employee in each dept [message #422170 is a reply to message #421988] |
Mon, 14 September 2009 23:16  |
 |
Kevin Meade
Messages: 2103 Registered: December 1999 Location: Connecticut USA
|
Senior Member |
|
|
Yes, the issue is you need to identify the rows you want to update correctly. Consider this query and result:
SQL> select deptno,empno,sal,min(sal) over (partition by deptno) min_sal
2 from emp
3 order by 1,3,2
4 /
DEPTNO EMPNO SAL MIN_SAL
---------- ---------- ---------- ----------
10 7934 1300 1300
7782 2450 1300
7839 5000 1300
20 7369 800 800
7876 1100 800
7566 2975 800
7788 3000 800
7902 3000 800
30 7900 950 950
7521 1250 950
7654 1250 950
7844 1500 950
7499 1600 950
7698 2850 950
14 rows selected.
From that we can easily find the rows to update:
SQL> l
1 select *
2 from (
3 select deptno,empno,sal,min(sal) over (partition by deptno) min_sal
4 from emp
5 )
6* where sal = min_sal
SQL> /
DEPTNO EMPNO SAL MIN_SAL
---------- ---------- ---------- ----------
10 7934 1300 1300
20 7369 800 800
30 7900 950 950
3 rows selected.
This however may not be the most effience method. Have a look over at the asktomhome site for his work on TOP N QUERIES. You will get a good discussion of this kind of problem in general.
Kevin
|
|
|