Deleting from a database map

Question

I have these 3 tables + data:

items: itemId, itemName
data: 1, my item one

categories: catId, catName
data: 1, my cat one. 2, my cat two

map: mapId, itemId, catId

When you include item "my item one" in category "my cat one", you insert [1, 1, 1] into the map. When you add "my item one" to "my cat two", you insert [2, 1, 2] into the map. Now let's say we change our mind and only want the item in "my cat two". This means we need to know what categories the item is no longer in and delete the associations from the map. What's the most efficient sequence of steps to take to do so? (I'm looking for a solution that will scale beyond this trivial example.)

Answer 1

Assuming you've already got the category ID for "my cat two" and the item ID for "my item 1":

DELETE FROM MAP WHERE ItemID = @MyItem1Id
                AND CatID <> @MyCat2Id

If you have a set of categories you want to keep the item in, you could either:

1. Delete everything from the map for that item and then re-add the set
2. Use a query like the above but with "AND CatID NOT IN [ ... ]"

Answer 2

If you decide that an item should only be in the new category, the following should work:

DELETE
     M
FROM
     Map M
WHERE
     M.itemid = @item_id AND
     M.catid <> @new_cat_id

Answer 3

It's not very clear what you mean by "scale beyond this trivial example".

Sorry for the confusion. I mean with the same tables but with far more data.