CODEWITHSUNDEEP
where-clauseoracle12csql-delete
Gangs165700
Gangs165700

Reputation: 131

Delete query with select query is not working as expected

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?

Upvotes: 0

Views: 1273

Answers (3)

Ankit Jindal
Ankit Jindal

Reputation: 4030

In your case, you need to use a reference key, a field in one table that refers to the PRIMARY KEY in another table.

Assuming pid as referenced key in table I_786 and fid as referenced key in B_786

Your query will look like this:

DELETE FROM B_786 WHERE B_786.fid IN (SELECT I_786.pid FROM I_786) as temp;

Upvotes: 1

Krishan Kumar
Krishan Kumar

Reputation: 403

DELETE FROM B_786 
WHERE  EXISTS (SELECT 1 FROM   I_786 where I_786.id = B_786.id);

Upvotes: 1

RKG
RKG

Reputation: 46

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.

Upvotes: 1

Related Questions