CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Incredible
Incredible

Reputation: 361

Update With Inner Join throwing SQL command not properly ended error

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;

Upvotes: 2

Views: 149

Answers (1)

krokodilko
krokodilko

Reputation: 36107

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

Upvotes: 2

Related Questions