在写MySQL的更新语句的时候出现报错:You can't specify target table 't_object_para' for update in FROM clause记录下来
MySQL中You can't specify target table 't_object_para' for update in FROM clause的错误意思是说,不能先select出同一个标志给你的某些只,然后在执行update这个表。例如下面的操作
update tbl set tbl.isShow=1 where id in
(
select max(id) from tbl a where EXISTS
(
select 1 from tbl b where a.tac=b.tac group by tac HAVING count(1)>1
)
group by tac
)
执行上面的SQL就会出现标题的报错
方法一:在外面套一层临时表
修改SQL如下
update tbl set isShow=1 where id in
(
select a.id from
(
select max(id) id from tbl a where EXISTS
(
select 1 from tbl b where a.tac=b.tac group by tac HAVING count(1)>1
)
group by tac
) a
)
方法二:本人在处理该问题的是方法一,在某种特定情况下处理不成功。所以用临时表的方法
DROP TABLE IF EXISTS tmp;
CREATE TEMPORARY table tmp(
select max(id) from tbl a where EXISTS(
select 1 from tbl b where a.tac=b.tac group by tac HAVING count(1)>1
)
group by tac
)
UPDATE ta1 set isShow=1 where id in (select id from tmp)