CODEWITHSUNDEEP
mysqlsqlleft-joinsql-delete
bradgonesurfing
bradgonesurfing

Reputation: 32162

improve a mysql query

I have a query.

DELETE FROM A  
WHERE i NOT IN 
( SELECT i FROM B WHERE j = 1  
  UNION select i from C 
  UNION select i from D 
);

Basically delete all rows in A where field i does not occur in tables B, C or D. If it was just:

DELETE FROM A  
WHERE i NOT IN 
( SELECT i FROM B 
);

Then that could be done easily with a left join

DELETE A FROM A 
LEFT JOIN B 
ON A.i = B.i
WHERE B.id is NULL;

( Assume that every table has a id field in the schema )

I guess my question is then does the above extend to the three table scenario with the following solution?

DELETE A FROM A 
LEFT JOIN B 
ON A.i = B.i AND B.j = 1
LEFT JOIN C
ON A.i = C.i 
LEFT JOIN D
ON A.i = D.i
WHERE B.id is NULL
AND   C.id is NULL
AND   D.id is NULL

Upvotes: 1

Views: 99

Answers (1)

onedaywhen
onedaywhen

Reputation: 57023

Something like this:

DELETE 
  FROM A
 WHERE NOT EXISTS (
                   SELECT * 
                     FROM B
                    WHERE B.i = A.i
                  )
       AND NOT EXISTS (
                       SELECT * 
                         FROM C
                        WHERE C.i = A.i
                      )
       AND NOT EXISTS (
                       SELECT * 
                         FROM D
                        WHERE D.i = A.i
                      );

Upvotes: 2

Related Questions