如何解决MySQL更新操作报错SQL Error [1093]与[1064]的问题

错误原因说明 #

• 报错1093：MySQL存在读写冲突防护限制，执行UPDATE操作时，不允许直接在FROM子句的子查询中引用当前要更新的目标表，因此原始SQL触发该报错。
• 报错1064：改写后的SQL存在语法顺序错误，MySQL多表UPDATE的语法规则要求SET赋值子句必须放在WHERE过滤子句之前，顺序颠倒就会触发语法校验失败。

正确改写方案 #

方案1：直接使用多表关联更新（性能最优，推荐） #

直接通过关联逻辑替代子查询，从根源避免同表引用问题，逻辑和原始需求完全一致：

UPDATE id_card ic
LEFT JOIN PROJECT p 
  ON p.PROJECT_code = ic.project 
  AND ic.VERSION = p.current_id_card_version 
SET ic.id_card_status = 'ACTIVE'
WHERE ic.project_type IN (670, 671, 672, 673)
  AND ic.id_card_status = 'DRAFT';

方案2：子查询套临时表兼容写法 #

如果业务逻辑必须保留子查询结构，只需给内层子查询额外套一层临时表封装，让MySQL将子查询结果识别为独立临时数据集，即可绕过1093限制：

UPDATE id_card 
SET id_card_status = 'ACTIVE' 
WHERE code IN (
  SELECT tmp.code FROM (
    SELECT ic.code FROM ID_CARD ic
    LEFT JOIN PROJECT p 
      ON p.PROJECT_code = ic.project 
      AND ic.VERSION = p.current_id_card_version 
    WHERE ic.project_type IN (670, 671, 672, 673)
      AND ic.id_card_status = 'DRAFT'
  ) AS tmp
);

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