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: 0
mysql> 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都没有这个问题。

本文地址 : http://www.linuxidc.com/Linux/2017-02/140209.htm

本文数据库(mysql)相关术语:navicat for mysql mysql workbench mysql数据库 mysql 存储过程 mysql安装图解 mysql教程 mysql 管理工具

主题: MySQLSQLLinuxInnoDB数据AUUTAUTRIMRY
分页:12
转载请注明
本文标题:MySQL 出现You can't specify target table for update in FROM clause错误解决方法
本站链接:http://www.codesec.net/view/530893.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mysql) | 评论(0) | 阅读(40)