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 --
精彩评论