delete records based on join result

Question

I have two tables that are joined by an id. Table A is where I define the records. Table B is where I use the definition and add some data. I am doing some data normalization and I have realized that on table B there are some ID that are no longer defined in table A.

If I run this query:

SELECT B.id_cred, A.id_cre from B LEFT JOIN A ON B.id_cred=A.id_cre

I see those records that are NULL on A.id_cre.

I'd like to DELETE from table B those records where the query returns null on table A?

Something like:

DELETE FROM B WHERE id IN (SELECT B.id from B LEFT JOIN A ON B.id_cred=A.id_cre WHERE a.id IS NULL)

but this query throws an error because table B is target and reference at the same time.

You can't specify target table B for UPDATE in FROM clause

Note that the join query will return 1408 rows so I need to do it in a massive way

looks similar to [Can't specify target table for update in FROM clause](http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause) — Imran Ali, Jan 26 '16 at 08:41
A SO would have helped you. Please check [this link](http://stackoverflow.com/questions/1980738/sql-delete-with-join-another-table-for-where-condition) — Ankit Tripathi, Jan 26 '16 at 08:43

score 2 · Accepted Answer · answered Jan 26 '16 at 08:40

2

Option 1, use NOT EXISTS:

delete from B
where not exists (select 1 from A where A.id_cre = B.id_cred)

Option 2, use a DELETE with JOIN:

delete B
from B
left join A on B.id_cred = A.id_cre
where A.id_cre is null

answered Jan 26 '16 at 08:40

lc.

113,939
20
158
187

Thanks. Not exists is what I was looking for. Didn't know this command :) – Lelio Faieta Jan 26 '16 at 08:44

score 0 · Answer 2 · answered Jan 26 '16 at 08:47

This should do the trick:

DELETE FROM B
WHERE NOT EXISTS (
SELECT id_cre
FROM A
WHERE B.id_cred=A.id_cre)

Just delete any row from B where the key does not exist in A.

NOTE: "A" and "B" can't be aliases, they must be the actual table names.

Hope this helps!

score -1 · Answer 3 · edited Jan 26 '16 at 11:45

-1

Why not use id_cre from A table since both have the id.

This statement:

DELETE FROM B WHERE id IN (SELECT B.id from B LEFT JOIN A ON B.id_cred=A.id_cre WHERE a.id IS NULL)

simply says that both a and b (B.id_cred=A.id_cre) are the same.

edited Jan 26 '16 at 11:45

Patt Mehta

4,110
1
23
47

answered Jan 26 '16 at 08:39

Amir

1
2

delete records based on join result

3 Answers3