mysql - 如何在 LEFT OUTER JOIN 后更新

Question

我从两个表中创建了一个 SELECT:

SELECT  p.products_id, p.gender, f.feature_set_id, f.products_id
FROM products_item_codes AS p
LEFT OUTER JOIN feature_set_to_products AS f ON p.products_id = f.products_id

products_id gender feature_set_id products_íd
       1235 Damen               7        1235
       1236 Damen               7        1236
       1237                  NULL        NULL
       1238 Herren           NULL        NULL
       1239 Herren           NULL        NULL
       1240 Damen               7        1240

如何更新 feature_set_id 和 products_id？

• gender='Herren' , feature_set_id='6'
• gender='Damen' , feature_set_id='7'

NULL 数据集不存在，我将创建它们。

Answer 1

我会使用 insert ... select ...语句来填充 feature_set_to_products 表。 case 语句可以处理根据性别插入适当的 feature_set_id。

insert into feature_set_to_products (feature_set_id, products_id)
select case when p.gender='Damen' then 7 when p.gender='Herren' then 6 else null end, p.products_id
from products_item_codes p
left join feature_set_to_products AS f ON p.products_id = f.products_id
where p.gender in ('Damen', 'Herren') and f.products_id is null