- A+
MySQL出现Mysql You can't specify target table 'XXX' for update in FROM clause,这个错误的意思是不能在同一个sql语句中,先select同一个表的某些值,然后再update这个表。
表结构:
CREATE TABLE `dhw_customer` ( `id` int(11) NOT NULL AUTO_INCREMENT, `type` tinyint(1) NOT NULL DEFAULT '1' COMMENT '1 个人 2企业 ', `name` varchar(55) DEFAULT NULL COMMENT '姓名 (联系人)', `position_name` varchar(55) DEFAULT NULL COMMENT '职位', `key_words` varchar(255) DEFAULT NULL COMMENT '关键词', `sex` tinyint(1) DEFAULT '0' COMMENT '性别 0 未知 1 男 2 女', `created_id` int(11) NOT NULL COMMENT '创建者', `service_id` int(11) NOT NULL COMMENT '操作者(属于者)', `remark` varchar(255) DEFAULT NULL COMMENT '备注', `created_at` int(11) DEFAULT NULL COMMENT '创建时间', `updated_at` int(11) DEFAULT NULL COMMENT '更新时间', `dispatch_id` int(11) DEFAULT NULL COMMENT '分派人id', `dispatch_state` tinyint(1) NOT NULL DEFAULT '0' COMMENT '分派状态 1 待分派 0 不需要分派 2 已分派', PRIMARY KEY (`id`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=157512 DEFAULT CHARSET=utf8mb4 COMMENT='客户表';
需求:要求把目前数据中service_id为0的数据以及dispatch_id为NULL的数据,dispatch_state状态改为1
原sql语句:
UPDATE dhw_customer SET dispatch_state = 1 WHERE id in (SELECT id FROM dhw_customer WHERE service_id = 0 AND dispatch_id IS NULL)
然后报错:
UPDATE dhw_customer SET dispatch_state = 1 WHERE id in (SELECT id FROM dhw_customer WHERE service_id = 0 AND dispatch_id IS NULL) > 1093 - You can't specify target table 'dhw_customer' for update in FROM clause > 时间: 0.003s
修改方案:先将需要修改的数据查出来,在select一次便可解决:
(SELECT m.id FROM ( SELECT id FROM dhw_customer WHERE service_id = 0 AND dispatch_id IS NULL ) m )
结果:
UPDATE dhw_customer SET dispatch_state = 0 WHERE id in (SELECT m.id FROM ( SELECT id FROM dhw_customer WHERE service_id = 0 AND dispatch_id IS NULL ) m ) > Affected rows: 27467 > 时间: 0.232s