2020-11-25 Mysql子查询问题：1093 - You can‘t specify target table ‘****‘ for update in FROM clause

最新推荐文章于 2022-08-23 16:03:07 发布

ywh22122

最新推荐文章于 2022-08-23 16:03:07 发布

阅读量167

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/ywh22122/article/details/110119780

版权

mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

Mysql不允许update表和子查询中表为同一张表

1.问题描述：

UPDATE refund SET state = 3 
WHERE id = 
(SELECT id FROM refund WHERE order_number = 'testNumber' ORDER BY create_time DESC LIMIT 0,1)

这条sql报错：
You can’t specify target table 'refund ’ for update in FROM clause
译为：不能在FROM子句中为update指定目标表“refund”

2.解决方法：嵌套子查询，给里面的表取别名

UPDATE refund SET state = 3 
WHERE id = 
(SELECT id FROM (SELECT id FROM refund WHERE order_number = 'testNumber' ORDER BY create_time DESC LIMIT 0,1)temp)

执行成功！

UPDATE refund SET state = 3
WHERE id =
(SELECT id FROM (SELECT id FROM refund WHERE order_number = ‘testNumber’ ORDER BY create_time DESC LIMIT 0,1)temp)
Affected rows: 0
时间: 0.025s

参考资料