You can't specify target table 'a' for update in FROM clause

项目中有一个功能变动上线，其中有一张表ttt的字段cc，历史数据需要处理，把字段cc中为’xxx’的值替换为'yyy'。

表A结构如下：

CREATE TABLE `ttt` (

  `id` bigint NOT NULL,

  `aa` int NOT NULL COMMENT 'xxx',

  `bb` int(11) NOT NULL COMMENT 'xxx',

  `cc` varchar(64) NOT NULL COMMENT 'xxx',

  ...

  PRIMARY KEY (`id`),

  UNIQUE KEY `uk_aa_bb_cc` (`aa`,`bb`,`cc`) USING BTREE,

) ENGINE=InnoDB DEFAULT CHARSET=utf8

更新sql如下：

UPDATE ttt SET cc='yyy' WHERE cc='xxx';

执行报错：

Duplicate entry 'xx-xx-yyy' for key 'uk_aa_bb_cc'

因为相同的aa、bb下可能已经有cc值为'yyy'的数据了，

比如已有历史数据：

aa bb cc

1 1 xxx

1 1 yyy

这个时候执行更新sql，就会有2条1 1 yyy，由于字段aa、bb、cc因业务属性设置为唯一索引，所以更新失败。

修改更新sql，将有相同yyy的数据排除掉：

UPDATE ttt a SET a.cc='yyy' WHERE a.cc='xxx'

AND NOT EXISTS (SELECT 1 FROM ttt b WHERE a.aa=b.aa AND a.bb=b.bb AND b.cc='yyy');

执行报错：

You can't specify target table 'a' for update in FROM clause

改成IN条件：

UPDATE ttt a SET a.cc='yyy' WHERE a.cc='xxx'

AND NOT IN (SELECT id FROM ttt b WHERE a.aa=b.aa AND a.bb=b.bb AND b.cc='yyy');

也报同样的错。

这个错误在MySQL会出现，在SQLServer、Oracle上没有，意思是：

不能先select出同一表中的某些值，再update这个表(在同一语句中)，即不能依据某字段值做判断再来更新某字段的值。

修改sql如下：

UPDATE ttt a SET a.cc='yyy' WHERE a.cc='xxx'

AND a.id NOT IN (

SELECT id FROM(SELECT id FROM ttt b WHERE a.aa=b.aa AND a.bb=b.bb AND b.cc='yyy')c

);

执行还是报错：

Unknown column 'a.aa' in 'where clause'

看来直接嵌套一层查询也不行。

再次修改sql，使用UPDATE JOIN语法：

UPDATE ttt a

LEFT JOIN ttt b ON a.aa=b.aa AND a.bb=b.bb AND b.cc='yyy'

SET a.cc='yyy'

WHERE a.cc='xxx' AND b.id IS NULL

因为需要排除掉相同记录，这里用LEFT JOIN并且条件为IS NULL的方式。

再次执行sql成功。

在执行sql前后可以用sql进行数据查询，比如：

查询没有相同aa、bb且ccc仅有'xxx'的数据(即预先验证上面的更新sql影响的数据情况)：

SELECT *

FROM ttt a

LEFT JOIN ttt b ON a.aa=b.aa AND a.bb=b.bb AND b.cc='yyy'

WHERE a.cc='xxx' AND b.id IS NULL

查询有相同aa、bb且cc存在xxx、yyy值的数据：

SELECT aa,bb,COUNT(*) FROM ttt

WHERE cc IN('xxx','yyy')

GROUP BY aa,bb

HAVING COUNT(*)>1

通过aa、bb条件查询：

SELECT * FROM ttt WHERE aa='xx' AND bb='yy';

参考：

MySQL 中 You can't specify target table '表名' for update in FROM clause错误解决办法

 MySQL update join语句

巴特西

You can't specify target table 'a' for update in FROM clause

最新文章

热门文章