This query works but is there any way of making it more elegant or speeding it up?
DELETE from bm_KW USING bm_KW, bmjn_KW WHERE bm_KW.KeywordID
IN(SELECT bmjn_KW.KeywordID FROM bmjn_KW
WHERE bmjn_KW.ItemID = '1016' ) AND bm_KW.KeywordID
NOT IN (SELECT bmjn_KW.KeywordID FROM bmjn_KW
WHERE bmjn_KW.ItemID != '1016');
Its purpose is to delete only the keywords which are unique to the item being deleted, "1016" in this case.
Try the below, may it seems more speedy.
DELETE bm_KW FROM bm_KW INNER JOIN
(SELECT kw2.KeywordID FROM bmjn_KW kw1
INNER JOIN bmjn_KW kw2 ON kw1.KeywordID = kw2.KeywordID
AND kw2.ItemID != '1016' WHERE kw1.ItemID = '1016'
) AS keywords
ON bm_KW.KeywordID = keywords.KeywordID;