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记录下来

---

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)