Clever Geek Handbook

Removing Database from Map

I have these 3 tables + data:

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

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

map : mapId, itemId, catId

When you include the "my one item" item in the "my cat one" category, 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 that we changed our minds and only want the element in "my cat two". This means that we need to know which categories the item is no longer in and remove associations from the map. What is the most effective sequence of steps to do this? (I'm looking for a solution that will depend on this trivial example.)

0


source to share


2 answers


Assuming you already have a category ID for "my cat two" and an item ID for "my item 1":

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


If you have a set of categories that you want to store an item in, you can:

  • Remove everything from the map for this item and then add the set again
  • Use a query like above, but with "AND CatID NOT IN [...]"
+3


source


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



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


source






More articles:


All Articles