mysql 出现You can't specify target table for update in FROM clause错误的解决方法
来源:互联网 发布:windows vista 镜像 编辑:程序博客网 时间:2024/06/05 08:24
mysql出现You can’t specify target table for update in FROM clause 这个错误的意思是不能在同一个sql语句中,先select同一个表的某些值,然后再update这个表。
例如:message表保存了多个用户的消息
创建表
CREATE TABLE `message` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `uid` int(10) unsigned NOT NULL, `content` varchar(255) NOT NULL, `addtime` datetime NOT NULL, PRIMARY KEY (`id`), KEY `uid` (`uid`), KEY `addtime` (`addtime`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
插入数据
insert into message(uid,content,addtime) values(1,'content1','2016-09-26 00:00:01'),(2,'content2','2016-09-26 00:00:02'),(3,'content3','2016-09-26 00:00:03'),(1,'content4','2016-09-26 00:00:04'),(3,'content5','2016-09-26 00:00:05'),(2,'content6','2016-09-26 00:00:06'),(2,'content7','2016-09-26 00:00:07'),(4,'content8','2016-09-26 00:00:08'),(4,'content9','2016-09-26 00:00:09'),(1,'content10','2016-09-26 00:00:10');
表结构及数据如下:
mysql> select * from message;+----+-----+-----------+---------------------+| id | uid | content | addtime |+----+-----+-----------+---------------------+| 1 | 1 | content1 | 2016-09-26 00:00:01 || 2 | 2 | content2 | 2016-09-26 00:00:02 || 3 | 3 | content3 | 2016-09-26 00:00:03 || 4 | 1 | content4 | 2016-09-26 00:00:04 || 5 | 3 | content5 | 2016-09-26 00:00:05 || 6 | 2 | content6 | 2016-09-26 00:00:06 || 7 | 2 | content7 | 2016-09-26 00:00:07 || 8 | 4 | content8 | 2016-09-26 00:00:08 || 9 | 4 | content9 | 2016-09-26 00:00:09 || 10 | 1 | content10 | 2016-09-26 00:00:10 |+----+-----+-----------+---------------------+10 rows in set (0.00 sec)
然后执行将每个用户第一条消息的内容更新为Hello World
mysql> update message set content='Hello World' where id in(select min(id) from message group by uid);ERROR 1093 (HY000): You can't specify target table 'message' for update in FROM clause
因为在同一个sql语句中,先select出message表中每个用户消息的最小id值,然后再更新message表,因此会出现 ERROR 1093 (HY000): You can’t specify target table ‘message’ for update in FROM clause 这个错误。
解决方法:select的结果再通过一个中间表select多一次,就可以避免这个错误
update message set content='Hello World' where id in( select min_id from ( select min(id) as min_id from message group by uid) as a );
执行:
mysql> update message set content='Hello World' where id in( select min_id from ( select min(id) as min_id from message group by uid) as a );Query OK, 4 rows affected (0.01 sec)Rows matched: 4 Changed: 4 Warnings: 0mysql> select * from message;+----+-----+-------------+---------------------+| id | uid | content | addtime |+----+-----+-------------+---------------------+| 1 | 1 | Hello World | 2016-09-26 00:00:01 || 2 | 2 | Hello World | 2016-09-26 00:00:02 || 3 | 3 | Hello World | 2016-09-26 00:00:03 || 4 | 1 | content4 | 2016-09-26 00:00:04 || 5 | 3 | content5 | 2016-09-26 00:00:05 || 6 | 2 | content6 | 2016-09-26 00:00:06 || 7 | 2 | content7 | 2016-09-26 00:00:07 || 8 | 4 | Hello World | 2016-09-26 00:00:08 || 9 | 4 | content9 | 2016-09-26 00:00:09 || 10 | 1 | content10 | 2016-09-26 00:00:10 |+----+-----+-------------+---------------------+10 rows in set (0.00 sec)
注意,只有mysql会有这个问题,mssql与oracle都没有这个问题。
1 0
- mysql 出现You can't specify target table for update in FROM clause错误的解决方法
- mysql错误:1093-You can’t specify target table for update in FROM clause的解决方法
- MySQL "You can't specify target table 'X' for update in FROM clause" 错误解决方法
- mysql中You can’t specify target table for update in FROM clause错误解决方法
- mysql中You can’t specify target table for update in FROM clause错误解决方法
- mysql 中 You can't specify target table <tbl> for update in FROM clause 错误解决方法
- mysql中You can’t specify target table for update in FROM clause错误解决方法
- mysql中You can’t specify target table for update in FROM clause错误解决方法
- mysql中You can’t specify target table for update in FROM clause错误解决方法
- mysql中You can’t specify target table for update in FROM clause错误解决方法
- mysql中You can’t specify target table for update in FROM clause错误解决方法
- 关于mysql "You can't specify target table 'test_zb_scene' for update in FROM clause"的错误
- mysql中错误:1093-You can’t specify target table for update in FROM clause的解决方法
- mysql You can't specify target table for update in FROM clause解决方法
- MySQL 出现You can't specify target table for update in FROM clause
- mysql中You can't specify target table for update in FROM clause错误
- Mysql 错误 Code: 1093. You can't specify target table for update in FROM clause
- mysql中You can't specify target table for update in FROM clause错误
- Android 开发环境下载地址 -- 百度网盘 adt-bundle android-studio sdk adt 下载
- C++中相对路径和绝对路径
- Java:类(对象)、继承
- Docker
- 主席树 专题
- mysql 出现You can't specify target table for update in FROM clause错误的解决方法
- Greenplum优化--数据库配置篇
- [JavaEE - JPA] 5. ORM的核心注解 - 访问方式,表映射以及主键生成
- python 原始列表直接去重的方法
- hihoCoder#1128 : 二分·二分查找
- poj--2446 Chessboard(二分图最大匹配)
- NRF24L01收发测试程序
- 数字滚动
- 使用Android Studio搭建Android集成开发环境