title: MySQL: insert into multiple columns using selects from different tables
body: <p>I have a products catalog that is imported directly to a website's database. Each product row lists its Category and Subcategory. After the products are imported, I run this SQL to add the distinct categories to the database (which are used for creating navigation URLs based on their ids):</p>
  
  <pre><code>INSERT INTO categories (name) SELECT Distinct category FROM catalog_products;
  </code></pre>
  
  <p>Now, I want to add the subcategories as well to it's own table, but with a reference to the ID of it's parent Category, which is what I'm having trouble with.</p>
  
  <p>Example: the product "SDH 20" belongs to the Category "Diagnose" (has an id 5 on the 'categories' table) and to the subcategory "Hematology". The 'subcategories' table will then have to list Hematology as having a categoryID of 5, which is the ID of "Diagnose" on the category table. Here is the SQL I've attempted:</p>
  
  <pre><code>INSERT INTO subcategories (name, idCategory) SELECT Distinct subcategory FROM catalog_products UNION ALL SELECT id from category WHERE categories.name = (SELECT catalog_products.category FROM catalog_products);
  </code></pre>
  
  <p>But I get a  <code>"#1242 - Subquery returns more than 1 row"</code> error.</p>
  
  <p>Is it possible to populate the 'subcategories' table's name and idCategory with one SQL that reads the catalog_products rows? If so, what is wrong with my SQL?</p>