Delete query with select query is not working as expected

Question

I have 2 tables, I_786 (100k records) and B_786(700k records). The I_786 table all records must be deleted from B_786 table. Here I table is derived from B table by taking the first 100k records of it by using rownum. After that i need to delete the same records which are in I table. I created the query as below,

DELETE FROM B_786 
WHERE  EXISTS (SELECT * 
               FROM   I_786); 

But it is deleting all the data from B_786 table. Here the where condition is not working. How to optimize the where clause here?

Answer 1

You should use some column on which basis you want to delete the record from table B_786, like if there are 2 columns in table B_786 (id name) and having 700k records, and there are 100k records I_786 with columns (id, name). So to delete the data from B_786 table which matches the record with I_786 table.

Delete from B_786 where id in (select id from I_786);

By executing above command data will be deleted from B_786 which matches the id from I_786.