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.

+1


source to share


3 answers


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.

+1


source


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

+2


source


Also, if you are worried about trips to the db, follow the steps in the stored procedure. Then you have one trip.

+2


source







All Articles