INSERT from a SELECT with WHERE - IF EXISTS?

I have the following INSERT

INSERT INTO `tbl_productcategorylink` 
(`pcl_p_id`, `pcl_cat_id`, `pcl_orderby`) 
    SELECT 
     `p_id` AS pcl_p_id,
   开发者_开发知识库  (SELECT `cat_id` FROM `tbl_categories` WHERE 
       CASE
          WHEN `tbl_products`.`p_gender` = 'female' THEN
               `cat_url_tag` = 'womens' 
          ELSE
           `cat_url_tag` = 'mens'
       END
     LIMIT 1) AS pcl_cat_id,
     1 AS pcl_orderby
FROM `tbl_products` 
WHERE `tbl_products`.`p_gender` = 'female' OR `tbl_products`.`p_gender` = 'male';

This is to add a link between a category and a product, any products with p_gender set as 'male' are added to the 'mens' category and similarly for the 'female' category.

How can I do this but only if a row doesn't already exist for the given product?

I essentially need to add something to my WHERE clause to determine if a row already exists:

 AND `exists` IS NULL

I have attempted to create this exists column in the SELECT part of the query, but this messes up the column count and makes the INSERT fail.

Has any suggestions on to achieve this?

Thanks.

INSERT 
  tbl_productcategorylink 
  (pcl_p_id, pcl_cat_id, pcl_orderby) 
SELECT 
  p.p_id, c.cat_id, 1
FROM 
  tbl_products p
  INNER JOIN tbl_categories c ON c.cat_url_tag = CASE p.p_gender 
                                                 WHEN 'female' THEN 'womens' 
                                                 ELSE 'mens' END
WHERE 
  p.p_gender IN ('female', 'male')
  AND NOT EXISTS (
    SELECT 1 FROM tbl_productcategorylink WHERE pcl_p_id = p.p_id
  )

Try a NOT EXISTS in the WHERE clause:

INSERT INTO `tbl_productcategorylink` 
(`pcl_p_id`, `pcl_cat_id`, `pcl_orderby`) 
    SELECT 
     `p_id` AS pcl_p_id,
     (SELECT `cat_id` FROM `tbl_categories` WHERE 
       CASE
          WHEN `tbl_products`.`p_gender` = 'female' THEN
               `cat_url_tag` = 'womens' 
          ELSE
           `cat_url_tag` = 'mens'
       END
     LIMIT 1) AS pcl_cat_id,
     1 AS pcl_orderby
FROM `tbl_products` 
WHERE `tbl_products`.`p_gender` = 'female' OR `tbl_products`.`p_gender` = 'male'
     AND NOT EXISTS (SELECT * FROM `tbl_productcategorylink` WHERE `pcl_p_id` = `tbl_products`.`p_id`);

You could use INSERT IGNORE to continue if the key exists. Assuming that there is a unique key on a column(s).

"Insert Ignore" is not efficient when the same SQL is re-executed at a later time, as it will attempt to re-insert ALL records. You MUST limit attempts to Insert to the smallest quantity possible, for best system performance.

Using sub-select queries is also inefficient, a "LEFT JOIN" is nearly always faster when working with large data sets. Sub-select queries become horrible in the SELECT clause. Use 'IFNULL()' in the SELECT clause around the fields from a LEFT JOINed table to provide default values for the missing/NULL records.

Tomalak's answer works, but here is a pure 'LEFT JOIN' example.

INSERT INTO tbl_productcategorylink (p_pid, pcl_cat_id, pcl_orderby )
SELECT prod.p_pid, IFNULL( cat.cat_id,0 ), 1 AS pcl_orderby
  FROM tbl_products                  AS prod 
  LEFT JOIN tbl_categories           AS cat  ON cat.cat_url_tag = IF(prod.p_gender='female','womans','mens')
  LEFT JOIN tbl_productcategorylink  AS pcl  ON pcl.pcl_p_id = prod.p_id
 WHERE prod.p_gender IN ('female','male')
   AND pcl.pcl_p_id IS NULL -- Optimizes to faster "NOT EXISTS IN (Select ... From pcl )"

This re-write is assuming that tbl_categories has 2 specific records: 'womans' and 'mens'. Notice the 'where' clause contains the "pcl_p_pid IS NULL" which optimizes to a faster version of "NOT EXISTS IN (Select * From tbl_productcategorylink ... ) " when working with very large datasets.

The Sub-select queries do one of two things:
a) creates a temporary table.
b) performs a 'look-up' into the sub-selected table for EACH record that COULD be in it, as the database walks through the primary 'From' table. This is exaggerated into horrible performance when a sub-select is in the 'SELECT' clause.

LEFT JOINs use the already efficient JOIN logic the database provides, with allowance for missing records.

-- J Jorgenson --