OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » MERGE (db 12.1.0.2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

MERGE [message #641978]

Fri, 28 August 2015 10:31

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

I'm stuck with a simple MERGE statement. The idea is that for every employee whose salary is less than the average salary of his department, I want to update his salary to the average. I can do it like this,

UPDATE emp e
SET    sal = (SELECT Avg(sal)
              FROM   emp f
              WHERE  f.deptno = e.deptno)
WHERE  sal < (SELECT Avg(sal)
              FROM   emp f
              WHERE  f.deptno = e.deptno);

but this attempt at using MERGE

merge INTO emp
USING (SELECT deptno,Avg(sal) deptavg
       FROM   emp
       GROUP  BY deptno) dept
ON (emp.deptno=dept.deptno)
WHEN matched THEN
  UPDATE SET emp.sal = dept.deptavg WHERE emp.sal < dept.deptavg;

errors out:

orclz>
orclz> merge INTO emp
  2  USING (SELECT deptno,Avg(sal) deptavg
  3         FROM   emp
  4         GROUP  BY deptno) dept
  5  ON (emp.deptno=dept.deptno)
  6  WHEN matched THEN
  7    UPDATE SET emp.sal = dept.deptavg WHERE emp.sal < dept.deptavg;
  UPDATE SET emp.sal = dept.deptavg WHERE emp.sal < dept.deptavg
                                          *
ERROR at line 7:
ORA-00904: "EMP"."SAL": invalid identifier


orclz>

What am I doing wrong? Surely something idiotic?

Report message to a moderator

Re: MERGE [message #641983 is a reply to message #641978]

Fri, 28 August 2015 11:37

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Nothing wrong I can see and it works in my version:

SQL> select * from emp order by deptno, sal;
     EMPNO ENAME      JOB              MGR HIREDATE           SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------- ---------- ---------- ----------
      7934 MILLER     CLERK           7782 23-JAN-1982       1300                    10
      7782 CLARK      MANAGER         7839 09-JUN-1981       2450                    10
      7839 KING       PRESIDENT            17-NOV-1981       5000                    10
      7369 SMITH      CLERK           7902 17-DEC-1980        800                    20
      7876 ADAMS      CLERK           7788 23-MAY-1987       1100                    20
      7566 JONES      MANAGER         7839 02-APR-1981       2975                    20
      7788 SCOTT      ANALYST         7566 19-APR-1987       3000                    20
      7902 FORD       ANALYST         7566 03-DEC-1981       3000                    20
      7900 JAMES      CLERK           7698 03-DEC-1981        950                    30
      7654 MARTIN     SALESMAN        7698 28-SEP-1981       1250       1400         30
      7521 WARD       SALESMAN        7698 22-FEB-1981       1250        500         30
      7844 TURNER     SALESMAN        7698 08-SEP-1981       1500          0         30
      7499 ALLEN      SALESMAN        7698 20-FEB-1981       1600        300         30
      7698 BLAKE      MANAGER         7839 01-MAY-1981       2850                    30

14 rows selected.

SQL> select deptno, avg(sal) from emp group by deptno order by deptno;
    DEPTNO   AVG(SAL)
---------- ----------
        10 2916.66667
        20       2175
        30 1566.66667

3 rows selected.

SQL> merge INTO emp
  2  USING (SELECT deptno,Avg(sal) deptavg
  3         FROM   emp
  4         GROUP  BY deptno) dept
  5  ON (emp.deptno=dept.deptno)
  6  WHEN matched THEN
  7    UPDATE SET emp.sal = dept.deptavg WHERE emp.sal < dept.deptavg;

8 rows merged.

SQL> select * from emp order by deptno, sal;
     EMPNO ENAME      JOB              MGR HIREDATE           SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 09-JUN-1981    2916.67                    10
      7934 MILLER     CLERK           7782 23-JAN-1982    2916.67                    10
      7839 KING       PRESIDENT            17-NOV-1981       5000                    10
      7876 ADAMS      CLERK           7788 23-MAY-1987       2175                    20
      7369 SMITH      CLERK           7902 17-DEC-1980       2175                    20
      7566 JONES      MANAGER         7839 02-APR-1981       2975                    20
      7902 FORD       ANALYST         7566 03-DEC-1981       3000                    20
      7788 SCOTT      ANALYST         7566 19-APR-1987       3000                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-1981    1566.67       1400         30
      7844 TURNER     SALESMAN        7698 08-SEP-1981    1566.67          0         30
      7521 WARD       SALESMAN        7698 22-FEB-1981    1566.67        500         30
      7900 JAMES      CLERK           7698 03-DEC-1981    1566.67                    30
      7499 ALLEN      SALESMAN        7698 20-FEB-1981       1600        300         30
      7698 BLAKE      MANAGER         7839 01-MAY-1981       2850                    30

14 rows selected.

SQL> @v

Version Oracle : 11.2.0.1.0

Report message to a moderator

Re: MERGE [message #641988 is a reply to message #641983]

Fri, 28 August 2015 13:59

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

Thankyou for doing the test. I've re-run utlsampl.sql and now it runs fine in my 12.1.0.2. I must have done something bizarre to my EMP table.

Report message to a moderator

Previous Topic:	Need data beween date range
Next Topic:	Struggling with Query holding temp space

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 16:34:05 CDT 2024