Delete from table where multiple fields match select subquery from other table

Question

I want to delete an entry in a table where multiple fields match the results of another select subquery which takes data from another table.

This is what I have so far, though it doesn't work:

DELETE FROM table1
WHERE table1.id IN
 (SELECT id
 FROM table1 a JOIN table2 b
 ON a.field1 = b.field1
 AND a.field2 = b.field2
 AND a.field3 = b.field3
 AND b.id = ?
 )

• ypercubeᵀᴹ: How it doesn't work? Is it working but slow? It is not deleting what you want? Is it throwing error?

Answer 1

Perhaps the DELETE JOIN would be preferable

DELETE A.* FROM table1 A INNER JOIN table2 b
ON A.id = B.id
AND a.field1 = b.field1
AND a.field2 = b.field2
AND a.field3 = b.field3
AND b.id = ?;

I wrote about why DELETE with WHERE involving a subquery is sort of unhealthy to deal with in a past post of mine back on Feb 22, 2011: Problem with MySQL subquery

Even though there have been great strides in improving the Query Optimizer, evaluation of subqueries could make keys unavailable for key comparisons downstream.

ALTERNATIVE

Try gathering the keys you know need to be deleted and do the DELETE JOIN with it:

CREATE TABLE DeleteIDs SELECT id FROM table1 WHERE 1=2;
INSERT INTO table1
 SELECT A.id FROM table1 A INNER JOIN table2 b
 ON A.id = B.id
 AND a.field1 = b.field1
 AND a.field2 = b.field2
 AND a.field3 = b.field3
 AND b.id = ?
;
ALTER TABLE DeleteIDs ADD PRIMARY KEY (id);
DELETE B.* FROM DeleteIDs A INNER JOIN table1 B;
DROP TABLE DeleteIDs;

SUGGESTION

Maybe an index would help

ALTER TABLE table1 ADD INDEX id123 (id,field1,field2,field3);
ALTER TABLE table2 ADD INDEX id123 (id,field1,field2,field3);

Give it a Try !!!

• Pacerier: Why do you write DELETE A.* FROM .... instead of DELETE A FROM ....? Isn't the * needlessly redundant?
• RolandoMySQLDBA: @Pacerier that's how I learned it 20 years doing Microsoft Access development. MySQL accepts it. So, it's just an old habit.
• Pacerier: However it's somewhat wrong since it seems to suggest that you can do a delete a.Column2 when really it deletes the entire row.
• ypercubeᵀᴹ: @Pacerier If it's in the SQL standard, - by definition - it is not wrong ;)