mysql 错误代码 1093 : You can't specify target table for update in FROM clause

Question

我可能做错了什么，我之前搜索过它，我找到了一些解决方法，告诉我它在 MySQL 上是不可能的，其他人发布说这是由于 MySQL 优化器，所以你可以简单地关闭它并继续，但这对我不起作用。

我只想要一个简单的解决方法来处理这个问题。我可以找到 id_address_delivery 和 id_address_invoice 的唯一表来自订单表，此数据也在其他表中，但可以为空，因此无法在其他地方搜索它。

SET optimizer_switch = 'derived_merge=off';

update orders 
set id_customer = (select id_customer from customer where email like 'foo@foo.com'),
id_address_delivery = (select id_address_delivery from orders where id_customer = (select id_customer from customer where email like 'foo@foo.com') LIMIT 1),
id_address_invoice = (select id_address_invoice from orders where id_customer = (select id_customer from customer where email like 'foo@foo.com') LIMIT 1)
where id_customer = (select id_customer from customer where email like 'foo2@foo2.com');

我收到错误代码:1093。您不能在 mysql workbench 上的 FROM 子句中指定要更新的目标表“订单”，即使我申请了

SET optimizer_switch = 'derived_merge=off';

有什么办法可以处理这种情况吗？我感谢设置一些变量，例如:

SET @iad = (select id_address_delivery from orders where id_customer = (select id_customer from customer where email like 'foo@foo.com') LIMIT 1);

然后将此 var 设置为如下值:

id_address_delivery = @iad;

我在执行此操作时没有收到错误响应，但它确实持续了很多时间(我完全不知道为什么)并且出现了超时消息(30 秒)我尝试将其设置为 120 秒并获得相同的超时消息。

编辑:

我尝试使用别名但没有结果。同样的错误:

update orders AS sor
set sor.id_customer = (select id_customer from customer where email like 'foo@foo.com'),
sor.id_address_delivery = (select a.id_address_delivery from orders as a where a.id_customer = (select id_customer from customer where email like 'foo@foo.com') LIMIT 1),
sor.id_address_invoice = (select b.id_address_invoice from orders as b where b.id_customer = (select id_customer from customer where email like 'foo@foo.com') LIMIT 1)
where pso.id_customer = (select id_customer from customer where email like 'foo2@foo2.com');

/编辑:

我阅读了一些被标记为同一问题的帖子，但我找到了其他条款的一些解决方法，但我无法弄清楚如何将相同的方法应用于我的特定案例。

我该如何继续？谢谢。

Answer 1

作为其他答案，如果您在表上执行 UPDATE/INSERT/DELETE，则不能在内部查询中引用该表(但是您可以引用该外部表中的字段...)

检查以下与您的问题相同且已解决的问题。

MySQL Error 1093 - Can't specify target table for update in FROM clause

You can't specify target table for update in FROM clause