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?

RKG · Accepted Answer · 2020-06-27 07:46:12Z

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.

answered Jun 27, 2020 at 7:46

RKG

463 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Krishan Kumar · Accepted Answer · 2020-06-27 07:58:21Z

1

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

answered Jun 27, 2020 at 7:58

Krishan Kumar

4035 silver badges15 bronze badges

Comments

Ankit Jindal · Accepted Answer · 2020-06-27 09:36:48Z

1

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;

edited Jun 27, 2020 at 9:36

answered Jun 27, 2020 at 9:00

Ankit Jindal

4,0963 gold badges32 silver badges41 bronze badges

Collectives™ on Stack Overflow

Delete query with select query is not working as expected

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related