MS Access Update SQL转MariaDB失败，求正确转换方案

原有MS Access的UPDATE语句用于标记重复条目，给LNCount字段赋值对应重复次数，语句如下：

UPDATE Exam SET Exam.LNCount = DCount("DUP","Exam","DUP =" & """" & [DUP] & """""")
WHERE (((Exam.Certificate_No) In (SELECT [Certificate_No] FROM [Exam] As Tmp  where [Certificate_No] Not Like "000*" GROUP BY [Certificate_No] HAVING Count(*)>1 )));

该语句在Access中可运行但速度极慢，尝试转换为MariaDB语句时多次失败：

• 尝试1：

  UPDATE Exam Set LNCOUNT = Count(DUP) 
  WHERE Certificate_No IN
  (SELECT Certificate_No FROM Exam As Tmp  where Certificate_No Not Like "000%" GROUP BY Certificate_No HAVING Count(*)>1 );
  

  错误：Invalid use of group（分组使用无效）
• 尝试2：

  UPDATE Exam Set LNCOUNT
  WHERE Certificate_No IN
  (SELECT Certificate_No FROM Exam As Tmp  where Certificate_No Not Like "000%" GROUP BY Certificate_No HAVING Count(*)>1 );
  

  错误：执行结果错误
• 尝试3：

  UPDATE Exam Set LNCOUNT = 
  (SELECT Certificate_No FROM Exam As Tmp  where Certificate_No Not Like "000%" GROUP BY Certificate_No HAVING Count(*)>1 );
  

  错误：Subquery returns more than 1 row（子查询返回多行）
• 额外尝试：在子查询前加IN，出现Truncated incorrect Decimal Value '099-10141'错误（该值为Certificate_No），其他如Count(DUP) AS X等写法也失败。

正确的MariaDB UPDATE语句 #

要实现和原Access语句相同的功能（给每个重复的DUP条目赋值其出现的总次数，且仅处理Certificate_No不以000开头且重复的记录），推荐两种可行写法：

方法1：JOIN分组统计（性能更优）

UPDATE Exam
JOIN (
    SELECT DUP, COUNT(*) AS dup_count
    FROM Exam
    WHERE Certificate_No NOT LIKE '000%'
    GROUP BY DUP
    HAVING COUNT(*) > 1
) AS dup_stats ON Exam.DUP = dup_stats.DUP
SET Exam.LNCount = dup_stats.dup_count
WHERE Exam.Certificate_No NOT LIKE '000%';

方法2：关联子查询

UPDATE Exam
SET LNCount = (
    SELECT COUNT(*)
    FROM Exam AS Tmp
    WHERE Tmp.DUP = Exam.DUP
      AND Tmp.Certificate_No NOT LIKE '000%'
)
WHERE Exam.Certificate_No NOT LIKE '000%'
  AND EXISTS (
      SELECT 1
      FROM Exam AS Tmp
      WHERE Tmp.Certificate_No = Exam.Certificate_No
      GROUP BY Tmp.Certificate_No
      HAVING COUNT(*) > 1
);

错误原因解释 #

1. 尝试1错误：Count(DUP)是聚合函数，不能直接在UPDATE的SET子句中单独使用——聚合函数需要对多行数据计算，而UPDATE是逐行执行的，必须配合分组或子查询才能返回单个值。
2. 尝试2错误：SET子句缺少赋值表达式，语法不完整，数据库无法识别要给LNCOUNT赋什么值。
3. 尝试3错误：子查询返回了多个Certificate_No值，但UPDATE的SET子句要求子查询只能返回单个值（逐行赋值），因此触发返回多行的错误。
4. 额外尝试错误：IN是WHERE条件的逻辑运算符，不能用于SET赋值的子查询前；同时Certificate_No字段类型可能定义为数值型，但实际存储了字符串内容，将字段类型改为VARCHAR即可避免类型转换错误。

性能优化建议 #

• 给DUP和Certificate_No字段添加索引，大幅提升分组和关联查询的速度：

  CREATE INDEX idx_exam_dup ON Exam(DUP);
  CREATE INDEX idx_exam_cert_no ON Exam(Certificate_No);
  

• 优先使用JOIN的方式，大数据量下比关联子查询性能更稳定。

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