3

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)
Manushree Mishra
  • 87
  • 1
  • 1
  • 9

1 Answers1

4

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    I still don't understand why can't i use emp1?
    and I am just practicing my concepts so trying to turn things around without having to make new tables.     – Manushree Mishra Sep 27 '15 at 17:13
  • @ManushreeMishra . . . You are using `emp1` in the outer query. You want to compare it to `dpt`, based on the rest of the logic in the query. – Gordon Linoff Sep 27 '15 at 17:16
  • This answer is more applicable - https://stackoverflow.com/questions/4015199/oracle-sql-update-if-exists-else-insert – Vasin Yuriy Oct 25 '19 at 06:09