July 14, 2006 at 7:17 am
many to many relationship:
We have three tables: a master, a category and a relation table. A master can have many categories and a category can have may masters. These combinations are stored in the relation table. The question: I need to delete a category, which in turn means I need to delete all references to that category in the relation table. No problem so far. Now I need to delete only those master records whose only relation was with the deleted category while leaving those master records who are also related to other categories besides the deleted category. Can someone point me in the right direction.
thanks dan
July 14, 2006 at 11:12 am
>>Now I need to delete only those master records whose only relation was with the deleted category
In that scenario, you'll have a record in Master, with no remaining records existing in the Relation table, right ?
DELETE M
FROM Master As M
WHERE NOT EXISTS (
SELECT *
FROM Relation As R
WHERE M.KeyColumns(s) = R.KeyColumn(s)
)
July 14, 2006 at 12:20 pm
That is exactly right. I was just trying to make it too complicated with joins, etc.
Thank you very much dan
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply