Update With Inner Join throwing SQL command not properly ended error

Question

I have two tables.

DEPARTMENT (TABLE)

DEPTID NUMBER(38)
NAME CHAR(20)
RANK NUMBER(38)

EMPLOYEE (TABLE)

EMPID NUMBER(38)
DEPTID NUMBER(38)
NAME CHAR(40)
AGE NUMBER(38)
SALARY NUMBER(38)

And I am trying to run following query, which is continuously throwing error. The query is to update Rank in Department table based on the Average age of employees in every department.

UPDATE d 
SET d.rank = T.rank 
FROM  department d
INNER JOIN 
     (SELECT
          rownum as rank, age, deptId 
      FROM 
          (SELECT 
               AVG(age) AS age, deptid 
           FROM employee
           GROUP by deptId
           ORDER BY age DESC)) AS T ON T.deptId = d.deptId;

not exactly duplication, since the question stops at ORA-01779 problem, and how to solve this issue. — Barbaros Özhan, Jan 27 '18 at 11:50
There may be duplicates of this question, but I don't think that the one chosen would help the OP solve this problem. — Gordon Linoff, Jan 27 '18 at 12:03

score 2 · Accepted Answer · answered Jan 27 '18 at 12:22

2

The syntax of UPDATE statement in Oracle does not support joins.

A MERGE statement can be used to do this task:

MERGE INTO DEPARTMENT d
USING (
     SELECT
             rownum as rank, age, deptId 
     FROM 
       (SELECT AVG(age) AS age, deptid 
        FROM employee
        GROUP by deptId
        ORDER BY age DESC)
) q
ON ( d.DEPTID = q.DEPTID )
WHEN MATCHED THEN UPDATE
SET d.RANK = q.rank

A working demo: http://sqlfiddle.com/#!4/f0f34/2

answered Jan 27 '18 at 12:22

krokodilko

35,300
7
55
79

Thanks krokodilko, I was writing exact same solution for my problem as you mentioned above. but, for some reason, it was giving me 0 results. – Incredible Jan 27 '18 at 12:49

Update With Inner Join throwing SQL command not properly ended error

1 Answers1