MySQL使用自连接更新ORDER_HEADER表时遇不可更新错误求助

问题说明 #

我在MySQL中尝试满足以下两个条件时更新订单标识：

• 同一收货地址（SHIP_TO_LINE1/SHIP_TO_LINE2/SHIP_TO_CITY/SHIP_TO_STATE_PROVINCE/SHIP_TO_POSTAL_CODE）和同一收货人（DELIVER_TO_NAME）的订单组中，所有订单的TERMS_TYPE_PORT_OR_PLACE标识已启用（值为'true'）；
• 该订单组中最早的创建日期已超过7天。

执行UPDATE语句时遇到错误：

ERROR: The target table ORDER_HEADER of the UPDATE is not updatable

原代码尝试用自连接但使用了派生表，导致无法更新，希望继续用JOIN而非子查询解决问题。

错误原因 #

1. 派生表不可更新：原代码中JOIN (SELECT * FROM ORDER_HEADER) AS OTHEROH创建了临时派生表，MySQL不允许直接更新这类临时表的字段；
2. SET语句语法错误：多个字段赋值需用逗号分隔，而非AND。

修正后的代码 #

UPDATE ORDER_HEADER AS OH
JOIN ORDER_HEADER AS OTHEROH 
    ON OH.SHIP_TO_LINE1 = OTHEROH.SHIP_TO_LINE1
    AND OH.SHIP_TO_LINE2 = OTHEROH.SHIP_TO_LINE2
    AND OH.SHIP_TO_CITY = OTHEROH.SHIP_TO_CITY
    AND OH.SHIP_TO_STATE_PROVINCE = OTHEROH.SHIP_TO_STATE_PROVINCE
    AND OH.SHIP_TO_POSTAL_CODE = OTHEROH.SHIP_TO_POSTAL_CODE
    AND OH.DELIVER_TO_NAME = OTHEROH.DELIVER_TO_NAME
SET 
    OH.VERBAL_CONFIRMATION_NAME = 'true',
    OTHEROH.VERBAL_CONFIRMATION_NAME = 'true'
WHERE
    OH.NUMBER <> OTHEROH.NUMBER 
    AND OH.CURRENT_STATUS = 'New' 
    AND OTHEROH.CURRENT_STATUS = 'New'
    AND OH.TERMS_TYPE_PORT_OR_PLACE = 'true' 
    AND OTHEROH.TERMS_TYPE_PORT_OR_PLACE = 'true' 
    -- 可选：仅更新未设置过的记录
    -- AND OH.VERBAL_CONFIRMATION_NAME IS NULL
    -- AND OTHEROH.VERBAL_CONFIRMATION_NAME IS NULL
    -- 确保订单组中最早的创建日期超过7天
    AND OTHEROH.CREATED_DATE <= NOW() - INTERVAL 1 WEEK 
    AND OH.CREATED_DATE >= OTHEROH.CREATED_DATE
-- 补充：确保同组所有订单的TERMS_TYPE_PORT_OR_PLACE都是true
AND NOT EXISTS (
    SELECT 1 FROM ORDER_HEADER AS CHECK_OH
    WHERE CHECK_OH.SHIP_TO_LINE1 = OH.SHIP_TO_LINE1
      AND CHECK_OH.SHIP_TO_LINE2 = OH.SHIP_TO_LINE2
      AND CHECK_OH.SHIP_TO_CITY = OH.SHIP_TO_CITY
      AND CHECK_OH.SHIP_TO_STATE_PROVINCE = OH.SHIP_TO_STATE_PROVINCE
      AND CHECK_OH.SHIP_TO_POSTAL_CODE = OH.SHIP_TO_POSTAL_CODE
      AND CHECK_OH.DELIVER_TO_NAME = OH.DELIVER_TO_NAME
      AND CHECK_OH.TERMS_TYPE_PORT_OR_PLACE != 'true'
);

关键调整点 #

• 去掉派生表：直接自连接ORDER_HEADER，两个别名都指向原表，避免临时表导致的不可更新问题；
• 修正SET语法：用逗号分隔多个字段的赋值操作；
• 补充全组校验：通过NOT EXISTS确保同组所有订单的TERMS_TYPE_PORT_OR_PLACE都是'true'，符合第一个前提要求；
• 保留JOIN逻辑：继续使用自连接关联同组订单，满足不用子查询的需求。

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