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;
Miles Thompson wrote:
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.
The bm_KW table stores the keywords and consists of two columns:
KeywordID and Keyword. KeywordID is an auto-incrementing primary key.
The bmjn_KW table stores only pointers to keywords and items and
consists of two columns: ItemID - a foreign key pointing to the id of a
given item - and KeywordID a foreign key pointing to the KeywordID in
the bm_KW table.
When an item is added the bm_KW table is searched to determine if any of
the keywords used to describe it have been used before. If so a record
is added to bmjn_KW referencing the item and the KeywordID in bm_KW.
If the keyword has not been used it is added to bm_KW and then
referenced as described above.
Any thoughts or opinions?
Regards - Miles Thompson
_________________________________________________________________
Win a trip for four to a concert anywhere in the world!
http://www.mobilelivetour.ca/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
