Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 = ?
   )
share|improve this question
    
How it doesn't work? Is it working but slow? It is not deleting what you want? Is it throwing error? –  ypercube Jun 4 '13 at 16:39
add comment

1 Answer

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 !!!

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.