title: Updating an associative table in MySQL
body: <p>Below is my (simplified) schema (in MySQL ver. 5.0.51b) and my strategy for updating it. There has got to be a better way. Inserting a new item requires 4 trips to the database and editing/updating an item takes up to <strong>7</strong>!</p>
  
  <p><strong>items</strong>: itemId, itemName
  <br /><strong>categories</strong>: catId, catName
  <br /><strong>map</strong>: mapId*, itemId, catId
  <br />* mapId (varchar) is concat of itemId + | + catId</p>
  
  <p>1) If inserting: insert item. Get itemId via MySQL API.
  <br />Else updating: just update the item table. We already have the itemId.</p>
  
  <p>2) Conditionally batch insert into <code>categories</code>.</p>
  
  <pre><code>INSERT IGNORE INTO categories (catName)
  VALUES ('each'), ('category'), ('name');
  </code></pre>
  
  <p>3) Select IDs from <code>categories</code>.</p>
  
  <pre><code>SELECT catId FROM categories
  WHERE catName = 'each' OR catName = 'category' OR catName = 'name';
  </code></pre>
  
  <p>4) Conditionally batch insert into <code>map</code>.</p>
  
  <pre><code>INSERT IGNORE INTO map (mapId, itemId, catId)
  VALUES ('1|1', 1, 1), ('1|2', 1, 2), ('1|3', 1, 3);
  </code></pre>
  
  <p>If inserting: we're done. Else updating: continue.</p>
  
  <p>5) It's possible that we no longer associate a category with this item that we did prior to the update. Delete old categories for this itemId.</p>
  
  <pre><code>DELETE FROM MAP WHERE itemId = 2
  AND catID &lt;&gt; 2 AND catID &lt;&gt; 3 AND catID &lt;&gt; 5;
  </code></pre>
  
  <p>6) If we have disassociated ourselves from a category, it's possible that we left it orphaned. We do not want categories with no items. Therefore, if <code>affected rows &gt; 0</code>, kill orphaned categories. I haven't found a way to combine these in MySQL, so this is #6 &amp; #7.</p>
  
  <pre><code>SELECT categories.catId
  FROM categories
  LEFT JOIN map USING (catId)
  GROUP BY categories.catId
  HAVING COUNT(map.catId) &lt; 1;
  </code></pre>
  
  <p>7) Delete IDs found in step 6.</p>
  
  <pre><code>DELETE FROM categories
  WHERE catId = 9
    AND catId = 10;
  </code></pre>
  
  <p>Please tell me there's a better way that I'm not seeing.</p>