MySQL 5.7中如何基于关联表批量更新装配的库存字段？

没问题，这个需求完全可以实现，而且有几种不同的写法，我来给你详细说明：

基础实现：使用UPDATE + 子查询 #

首先，我们可以通过关联两张表，配合子查询来获取关键组件的库存值，再更新装配的库存字段。核心逻辑是：找到每个装配对应的关键组件名称，再从assemblies_details中取出该组件的库存，赋值给装配的stock字段。

UPDATE assemblies_details ad
INNER JOIN assemblies_attributes aa ON ad.id = aa.id
SET ad.stock = (
    -- 根据attribute1指定的组件名称，查询其库存
    SELECT stock 
    FROM assemblies_details 
    WHERE name = aa.attribute1
);

说明： #

• INNER JOIN确保只更新那些在assemblies_attributes中有对应记录的装配（如果有些装配没有配置关键组件，可以换成LEFT JOIN，但未配置的装配stock会被设为NULL，需根据业务需求调整）。
• 子查询会为每个装配单独执行一次，获取对应组件的库存值。

更高效的方案：使用多表JOIN替代子查询 #

当数据量较大时，子查询可能会重复执行影响效率，我们可以用多表JOIN的方式直接关联组件的库存记录，避免子查询的重复开销：

UPDATE assemblies_details ad
-- 关联装配属性表，获取依赖的组件名称
INNER JOIN assemblies_attributes aa ON ad.id = aa.id
-- 关联组件的库存记录
INNER JOIN assemblies_details components ON components.name = aa.attribute1
-- 直接赋值组件库存给装配
SET ad.stock = components.stock;

优化建议： #

1. 添加索引提升速度：因为我们频繁根据name字段查询组件库存，建议给assemblies_details.name加索引：

   CREATE INDEX idx_assemblies_details_name ON assemblies_details(name);
   

2. 先预览再更新：执行UPDATE前，建议先通过SELECT语句验证结果是否符合预期，避免误操作：

   SELECT 
       ad.id AS assembly_id,
       ad.name AS assembly_name,
       aa.attribute1 AS component_name,
       components.stock AS component_stock
   FROM assemblies_details ad
   INNER JOIN assemblies_attributes aa ON ad.id = aa.id
   INNER JOIN assemblies_details components ON components.name = aa.attribute1;
   

3. 处理组件不存在的情况：如果attribute1中指定的组件名称在assemblies_details中不存在，上述语句会跳过这些装配（因为INNER JOIN会过滤掉无匹配的记录）。如果需要对这类装配做特殊处理（比如设为0），可以换成LEFT JOIN并添加条件：

   UPDATE assemblies_details ad
   LEFT JOIN assemblies_attributes aa ON ad.id = aa.id
   LEFT JOIN assemblies_details components ON components.name = aa.attribute1
   SET ad.stock = COALESCE(components.stock, 0); -- 组件不存在时设为0
   

内容的提问来源于stack exchange，提问作者Engineerd