UPDATE and EXISTS Clause

Question

Please help me identify what I am doing wrong in the query below :

The following code gives an error: ORA-00904 : DPT.DEPTNO : invalid identifier

UPDATE emp1 
SET ename = (SELECT dname
FROM dpt 
WHERE dpt.deptno = emp1.deptno)
WHERE EXISTS (SELECT ename
FROM emp1 
WHERE emp1.deptno = dpt.deptno);

the first half of the query works fine on its own :

UPDATE emp1 
SET ename = (SELECT dname
FROM dpt 
WHERE dpt.deptno = emp1.deptno)

Answer 1

You have the wrong table in the second subquery:

UPDATE emp1 
SET ename = (SELECT dname FROM dpt WHERE dpt.deptno = emp1.deptno)
WHERE EXISTS (SELECT 1 FROM dpt WHERE emp1.deptno = dpt.deptno);
----------------------------^

It is curious that you are setting a column called ename to the name of what is presumably a department. But that is another matter.