Home » SQL & PL/SQL » SQL & PL/SQL » update salary for lowest paid employee in each dept (Oracel10g)
update salary for lowest paid employee in each dept [message #421988] Sat, 12 September 2009 15:26 Go to next message
shrinika
Messages: 266
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 #421989 is a reply to message #421988] Sat, 12 September 2009 16:03 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
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.
Re: update salary for lowest paid employee in each dept [message #422006 is a reply to message #421988] Sun, 13 September 2009 00:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, the following condition is totally useless (assuming you have the correct SCOTT schema and its FK):
and deptno = (select deptno from dept c where c.deptno = a.deptno)

Regards
Michel
Re: update salary for lowest paid employee in each dept [message #422018 is a reply to message #421989] Sun, 13 September 2009 09:05 Go to previous messageGo to next message
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 Go to previous message
Kevin Meade
Messages: 2101
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
Previous Topic: display date by increment of one minute
Next Topic: Please advice on this solution
Goto Forum:
  


Current Time: Mon Dec 05 21:18:13 CST 2016

Total time taken to generate the page: 0.12360 seconds