phpMyAdmin下带GROUP BY、HAVING COUNT的SQL UPDATE语句编写求助

问题排查 #

• 筛选条件逻辑错误：你当前写的reference like '%-R' and reference <> '%-R'存在两个问题：第一，<> 运算符无法识别%通配符，该条件实际含义是匹配结尾为-R、且值不等于字面量字符串%-R的记录，如果你是想排除结尾为-R的记录，应该写reference NOT LIKE '%-R'；第二，如果你同时写了LIKE '%-R'和NOT LIKE '%-R'，两个条件完全互斥，会导致没有符合条件的记录，这是UPDATE无效果的核心原因，请根据你的实际筛选需求保留对应条件即可。
• UPDATE语法错误：你直接把子查询放在WHERE子句中，没有和外层要更新的表做关联匹配，无法正确识别需要更新的行，需要用EXISTS或者关联查询的方式做行匹配。
• 额外兼容问题：如果你的数据库开启了ONLY_FULL_GROUP_BY SQL模式，原始SELECT * GROUP BY id_product的写法会触发语法报错，需要明确匹配行的主键ID来规避问题。

正确UPDATE语句 #

以下写法默认你需要筛选reference LIKE '%-R'的记录，如果你实际需要的是排除这类记录，自行替换为reference NOT LIKE '%-R'即可。

写法1：兼容大部分数据库的EXISTS写法 #

UPDATE prstshp_stock_available2 t1
SET quantity = (quantity / imultiply) - 1
WHERE EXISTS (
    SELECT 1
    FROM prstshp_stock_available2 t2
    WHERE t2.reference LIKE '%-R'
    AND t2.id_product = t1.id_product
    GROUP BY t2.id_product
    HAVING COUNT(*) = 1
);

写法2：支持窗口函数的数据库可用（更稳妥） #

UPDATE prstshp_stock_available2 t1
JOIN (
    SELECT id,
           COUNT(*) OVER (PARTITION BY id_product) AS product_cnt
    FROM prstshp_stock_available2
    WHERE reference LIKE '%-R'
) t2 ON t1.id = t2.id
SET t1.quantity = (t1.quantity / t1.imultiply) - 1
WHERE t2.product_cnt = 1;

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