MySQL批量更新异常：是否需CURSOR？CASE语句失效求助

尝试遍历表并根据查询条件更新term_id属性，但执行当前语句时所有值都被设为52，说明CASE语句仅匹配第一个条件并全局应用；若去掉CASE中SELECT的COUNT，会报错返回多行。单独执行每个SELECT语句时，能正确返回需要更新的行（如第一个SELECT对应41行需设为'52'，第二个对应14行设为'51'等）。需求是：遍历SELECT结果中的每一行，更新t1.term_id为对应值，条件为t1.taxonomy='pa_supplier'且t1.product_id=table2.product_id、table2.sku=table4.VendorStockCode。

原SQL语句：

UPDATE table1 t1
SET t1.term_id = 
(
    CASE
        WHEN 
            (
SELECT COUNT(VendorStockCode) FROM table4 t4
JOIN table5 t5 WHERE t4.VendorStockCode= t5.Manufacture_Code AND EXISTS(SELECT sku FROM table2 WHERE table4.VendorStockCode = table2.sku) AND t4.StockAvailable > 0 AND t5.Stock_qty <= 0
            ) > 0
        THEN '52'
        WHEN 
            (
SELECT COUNT(VendorStockCode) FROM table4 t4
JOIN table5 t5 WHERE t4.VendorStockCode= t5.Manufacture_Code AND EXISTS(SELECT sku FROM table2 WHERE table4.VendorStockCode = table2.sku) AND t4.StockAvailable <= 0 AND t5.Stock_qty > 0
            ) > 0
        THEN '51'
        WHEN 
            (
SELECT COUNT(VendorStockCode) FROM table4 t4
JOIN table5 t5 WHERE t4.VendorStockCode= t5.Manufacture_Code AND EXISTS(SELECT sku FROM table2 WHERE table4.VendorStockCode = table2.sku) AND t4.StockAvailable > 0 AND t5.Stock_qty > 0 AND CAST(t4.DealerEx as decimal(10,2)) > cast(t5.ExTax as decimal(10,2))
            ) > 0
        THEN '51'
                WHEN 
            (
SELECT COUNT(VendorStockCode) FROM table4 t4
JOIN table5 t5 WHERE t4.VendorStockCode= t5.Manufacture_Code AND EXISTS(SELECT sku FROM table2 WHERE table4.VendorStockCode = table2.sku) AND t4.StockAvailable > 0 AND t5.Stock_qty > 0 AND CAST(t4.DealerEx as decimal(10,2)) <= cast(t5.ExTax as decimal(10,2))
            ) > 0
        THEN '52'
        ELSE t1.term_id
    END
)
WHERE t1.taxonomy= 'pa_supplier';           

你的问题核心在于当前CASE里的子查询是全局统计，不是针对每一行t1的匹配判断，所以只要第一个COUNT>0，所有行都会被设为52。要实现按行匹配，需要把t1和关联表绑定，针对每一行t1做条件判断。

可以用UPDATE多表关联的方式，先把每个product_id对应的term_id计算逻辑整理成一个子查询，再关联table1进行更新：

UPDATE table1 t1
JOIN (
    SELECT 
        t2.product_id,
        CASE
            -- 按单条sku的条件判断，而非全局统计
            WHEN t4.StockAvailable > 0 AND t5.Stock_qty <= 0 THEN '52'
            WHEN t4.StockAvailable <= 0 AND t5.Stock_qty > 0 THEN '51'
            WHEN t4.StockAvailable > 0 AND t5.Stock_qty > 0 THEN 
                IF(CAST(t4.DealerEx AS DECIMAL(10,2)) > CAST(t5.ExTax AS DECIMAL(10,2)), '51', '52')
            ELSE NULL -- 无匹配条件时返回NULL
        END AS new_term_id
    FROM table2 t2
    JOIN table4 t4 ON t2.sku = t4.VendorStockCode
    JOIN table5 t5 ON t4.VendorStockCode = t5.Manufacture_Code
) AS sub ON t1.product_id = sub.product_id
SET t1.term_id = sub.new_term_id
WHERE t1.taxonomy = 'pa_supplier';

如果存在部分t1的product_id在关联表中没有匹配的情况，要保留原值，可以用LEFT JOIN代替JOIN：

UPDATE table1 t1
LEFT JOIN (
    SELECT 
        t2.product_id,
        CASE
            WHEN t4.StockAvailable > 0 AND t5.Stock_qty <= 0 THEN '52'
            WHEN t4.StockAvailable <= 0 AND t5.Stock_qty > 0 THEN '51'
            WHEN t4.StockAvailable > 0 AND t5.Stock_qty > 0 THEN 
                IF(CAST(t4.DealerEx AS DECIMAL(10,2)) > CAST(t5.ExTax AS DECIMAL(10,2)), '51', '52')
            ELSE NULL -- 无匹配条件时返回NULL
        END AS new_term_id
    FROM table2 t2
    JOIN table4 t4 ON t2.sku = t4.VendorStockCode
    JOIN table5 t5 ON t4.VendorStockCode = t5.Manufacture_Code
) AS sub ON t1.product_id = sub.product_id
SET t1.term_id = COALESCE(sub.new_term_id, t1.term_id) -- 有新值则更新，无则保留原值
WHERE t1.taxonomy = 'pa_supplier';

关键说明： #

• 去掉了全局COUNT统计，改为针对每个product_id对应的sku关联数据做行级判断
• 用多表JOIN替代子查询赋值，确保每个t1行只匹配自己对应的关联数据
• 用COALESCE处理无匹配数据的情况，避免覆盖原值

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