MySQL 出现You can't specify target table for update in FROM clause错误解决方法

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

例子：

CREATE TABLE `ppos_tmp3` (

`id`  int(11) NOT NULL AUTO_INCREMENT ,

`business_id`  int(11) NOT NULL ,

`card_num`  bigint(18) NULL DEFAULT NULL ,

`p_card_num`  char(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,

PRIMARY KEY (`id`)

)

;

insert into ppos_tmp3 select id,business_id,card_num,p_card_num from ppos_member_card_src a

where (a.business_id,a.p_card_num) in (select business_id,p_card_num from ppos_member_card_src group by business_id,p_card_num having count(*) > 1)

and id not in (select min(id) from ppos_member_card_src group by business_id,p_card_num having count(*)>1) and activate_stadium_id=100004;

delete from ppos_member_card_src where id in (select id from ppos_tmp3) and activate_stadium_id=100004;

MySQL 出现You can't specify target table for update in FROM clause错误解决方法的相关教程结束。