Mysql You can’t specify target table ‘XXX’ for update in FROM clause 报错问题解决方案

  • 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


发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: