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