MySQL同表更新触发1093错误，如何实现指定条件更新？

你原来的语句有两个问题：一是SET owner_id = owner_id等于没有更新任何值，二是直接在WHERE子查询中引用了被更新的表，触发了1093错误。

错误原因 #

MySQL不允许在UPDATE/DELETE语句的WHERE子查询里直接引用被操作的目标表，需要通过临时表或者JOIN的方式绕开这个限制。

方案一：使用包装后的子查询 #

将子查询包装成临时表，同时修正SET部分的逻辑，从其他账户的相同code记录中获取owner_id：

UPDATE portfolio_identifier pi
SET owner_id = (
    -- 从其他账户取相同code的owner_id，LIMIT 1避免同一个code对应多个owner_id的情况
    SELECT owner_id
    FROM portfolio_identifier
    WHERE code = pi.code AND account_id <> 139
    LIMIT 1
)
WHERE account_id = 139
AND EXISTS (
    -- 用包装后的临时表检查该code存在于其他账户中
    SELECT 1
    FROM (SELECT code FROM portfolio_identifier WHERE account_id <> 139) AS temp_codes
    WHERE temp_codes.code = pi.code
);

方案二：使用JOIN更新（更高效） #

通过JOIN关联目标表和其他账户的记录，直接更新owner_id，这种方式性能更好：

UPDATE portfolio_identifier pi
JOIN (
    -- 按code分组确保每个code只取一个owner_id，若同一个code有多个owner_id，可改用MIN/MAX指定取值
    SELECT code, MIN(owner_id) AS target_owner_id
    FROM portfolio_identifier
    WHERE account_id <> 139
    GROUP BY code
) AS temp ON pi.code = temp.code
SET pi.owner_id = temp.target_owner_id
WHERE pi.account_id = 139;

注意事项 #

如果同个code在其他账户下对应多个不同的owner_id，需要明确指定取值规则（比如取最小/最大的owner_id），否则结果可能不符合预期。

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