我有两张桌子
product
(product_id,category_id)product_options
(option_id,product_id)我想插入这些产品在product_options
其category_id
=%%
任何建议将不胜感激
这是我的查询:
select (select product_id
from oc_product
where oc_product.product_id = oc_product_option.product_id) as product_id,
15 as option_id, '' as value,
0 as required
from oc_product_option
inner join oc_product_to_category c
on oc_product_option.product_id = c.product_id
where c.category_id = 98
我给出了4条记录,但是我想要所有产品category_id = 98
,因为我知道我有很多产品category_id = 98
您应该将其插入oc_product_option
表中,而不要与之连接。那只会返回已经具有选项的产品,而不是类别中的所有产品。
INSERT INTO oc_product_option (product_id, option_id, value, required)
SELECT p.product_id, 15 AS option_id, '' AS value, 0 AS required
FROM oc_product AS p
JOIN oc_product_to_category AS c ON o.product_id = c.product_id
WHERE c.category_id = 98
实际上,甚至没有任何理由加入oc_product
,因为您没有使用该表中的任何内容。您可以这样做:
INSERT INTO oc_product_option (product_id, option_id, value, required)
SELECT product_id, 15 AS option_id, '' AS value, 0 AS required
FROM oc_product_to_category
WHERE category_id = 98
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句