Updating an associative table in MySQL
Below is my (simplified) schema (in MySQL version 5.0.51b) and my strategy for updating it. There must be a better way. To enter a new element requires 4 trips to the database, and edit / update element takes 7 !
elements : itemId, itemName
categories : catId, catName
map : mapId *, itemId, catId
* mapId (varchar) - concat itemId + | + catId
1) If insert: insert element. Get itemId via MySQL API.
Easy update: just update the element table. We already have itemId.
2) Conditional batch insert in categories
.
INSERT IGNORE INTO categories (catName)
VALUES ('each'), ('category'), ('name');
3) Select ids from categories
.
SELECT catId FROM categories
WHERE catName = 'each' OR catName = 'category' OR catName = 'name';
4) Conditionally insert the package into map
.
INSERT IGNORE INTO map (mapId, itemId, catId)
VALUES ('1|1', 1, 1), ('1|2', 1, 2), ('1|3', 1, 3);
Insert: we're done. Rest of the update: continue.
5) We may no longer associate a category with this element, which we did before the update. Remove old categories for this item.
DELETE FROM MAP WHERE itemId = 2
AND catID <> 2 AND catID <> 3 AND catID <> 5;
6) If we separate from the category, we may have left it orphaned. We don't want categories without elements. Therefore, if affected rows > 0
, kill the orphaned categories. I haven't found a way to combine them in MySQL, so this is # 6 and # 7.
SELECT categories.catId
FROM categories
LEFT JOIN map USING (catId)
GROUP BY categories.catId
HAVING COUNT(map.catId) < 1;
7) Delete identifiers found in step 6.
DELETE FROM categories
WHERE catId = 9
AND catId = 10;
Please tell me the best way I can't see.
source to share
Steps 6 and 7 can be easily combined:
DELETE categories.*
FROM categories
LEFT JOIN map USING (catId)
WHERE map.catID IS NULL;
Steps 3 and 4 can also be combined:
INSERT IGNORE INTO map (mapId, itemId, catId)
SELECT CONCAT('1|', c.catId), 1, c.catID
FROM categories AS c
WHERE c.catName IN('each','category','name');
Otherwise, your solution is pretty standard if you don't want to use triggers to save the map table.
source to share
There are several things you can do to make it a little easier:
-
Read about [
INSERT...ON DUPLICATE KEY UPDATE
] [1] -
Delete old categories before inserting new categories. This can improve the index.
DELETE FROM map WHERE itemId=2
; -
You probably don't need to
map.mapID
. Instead, declare a composite primary key above(itemID, catID)
. -
As Peter says in his answer, use MySQL multi-table delete:
DELETE categories.* FROM categories LEFT JOIN map USING (catId) WHERE map.catID IS NULL
http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html
source to share