Mysql You can't specify target table '表名' for update in FROM clause错误解决方案
测试表结构及测试数据
-- 示例:创建测试表
CREATE TABLE `b_schedule` (
`id` varchar(255) NOT NULL COMMENT '主键ID',
`name` varchar(255) DEFAULT NULL COMMENT '事项名称',
`code` varchar(255) DEFAULT NULL COMMENT '编码',
`parent_code` text COMMENT '父级编码',
`status` varchar(64) DEFAULT NULL COMMENT '状态',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='业务事项表';
-- 初始化测试数据
INSERT INTO `zciid-deliverable`.`b_schedule` (`id`, `name`, `code`, `parent_code`, `status`) VALUES ('001', '测试1', '1', 'root', NULL);
INSERT INTO `zciid-deliverable`.`b_schedule` (`id`, `name`, `code`, `parent_code`, `status`) VALUES ('002', '测试1.1', '1.1', '001', NULL);
INSERT INTO `zciid-deliverable`.`b_schedule` (`id`, `name`, `code`, `parent_code`, `status`) VALUES ('003', '测试1.1.1', '1.1.1', '001,002', NULL);
INSERT INTO `zciid-deliverable`.`b_schedule` (`id`, `name`, `code`, `parent_code`, `status`) VALUES ('004', '测试1.2', '1.2', '001', NULL);
INSERT INTO `zciid-deliverable`.`b_schedule` (`id`, `name`, `code`, `parent_code`, `status`) VALUES ('005', '测试1.2.1', '1.2.1', '001,004', NULL);
INSERT INTO `zciid-deliverable`.`b_schedule` (`id`, `name`, `code`, `parent_code`, `status`) VALUES ('006', '测试1.2.2', '1.2.2', '001,004', NULL);
INSERT INTO `zciid-deliverable`.`b_schedule` (`id`, `name`, `code`, `parent_code`, `status`) VALUES ('007', '测试1.2.2.1', '1.2.2.1', '001,004,006', NULL);
表测试数据如下:
id | name | code | parent_code | status |
---|---|---|---|---|
001 | 测试1 | 1 | root | |
002 | 测试1.1 | 1.1 | 001 | |
003 | 测试1.1.1 | 1.1.1 | 001,002 | |
004 | 测试1.2 | 1.2 | 001 | |
005 | 测试1.2.1 | 1.2.1 | 001,004 | |
006 | 测试1.2.2 | 1.2.2 | 001,004 | |
007 | 测试1.2.2.1 | 1.2.2.1 | 001,004,006 |
现有需求如下:
1. 更新 code 开始以 1.2 的数据 status 的值为 1;
1.1 错误的写法(及报错信息);
UPDATE `b_schedule`
SET status = '1'
-- SET detail_floor = NULL
WHERE
id in (
SELECT id FROM `b_schedule`
WHERE
`code` LIKE CONCAT((SELECT code from `b_schedule` where id = '004'),'%')
);
-- 报错信息如下
[Err] 1093 - You can't specify target table 'b_schedule' for update in FROM clause
1.2 原因分析:
在MYSQL里,不能先select出同一表中的某些值,再update这个表(在同一语句中),即不能依据
某字段值 做判断再来更新某字段的值,解决办法是,将select得到的结果,再通过中间表select一遍,
这样就规避了错误,这个问题只出现于mysql,mssql和oracle不会出现此问题。
2. 更新 code 开始以 1.2 的数据 status 的值为 1;
2.1 正确的写法;
UPDATE `b_schedule`
SET status = '1'
-- SET detail_floor = NULL
WHERE
id in (
SELECT id from (
SELECT id FROM `b_schedule`
WHERE
`code` LIKE CONCAT((SELECT code from `b_schedule` where id = '004'),'%')
) t1
);
执行结果如下:
id | name | code | parent_code | status |
---|---|---|---|---|
001 | 测试1 | 1 | root | |
002 | 测试1.1 | 1.1 | 001 | |
003 | 测试1.1.1 | 1.1.1 | 001,002 | |
004 | 测试1.2 | 1.2 | 001 | 1 |
005 | 测试1.2.1 | 1.2.1 | 001,004 | 1 |
006 | 测试1.2.2 | 1.2.2 | 001,004 | 1 |
007 | 测试1.2.2.1 | 1.2.2.1 | 001,004,006 | 1 |