CODEWITHSUNDEEP
sqloracle-databaseoracle10goracle11g
user472625
user472625

Reputation: 163

ERROR ::QUERY FAILING in DELETE STATEMENT

I have a requirement that I want rows selected based on a condition from table A and table B must be deleted.

For example EMP and EMP1 are two tables

Merge into emp1 a 
using (select * from emp) b 
  on (a. empno =b.empno)
WHEN MATCHED THEN DELETE
where(b.LOC='NEW YORK');

The above query results in error. If I use Where exists, all rows are deleted in Table A, which is not a right solution.

delete from emp1 a 
where exists
      ( select null 
        from emp b 
        where a. empno =b.empno 
          and b.LOC='NEW YORK' 
      );

Please suggest

Upvotes: 1

Views: 113

Answers (2)

James
James

Reputation: 539

I am not familiar with the MERGE statement, but I will say that emp b isn't properly declared because the b is outside the parenthesis.

Also, I would suggest first making a view (or a temporarily joined table) to be able to see what items match up before executing a DELETE command. Then you can use DELETE based on the view and easily delete from both tables.

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115510

DELETE FROM emp1 a 
WHERE a.empno IN 
      ( SELECT b.empno 
        FROM emp b 
        WHERE b.LOC = 'NEW YORK' 
      );

Upvotes: 3

Related Questions