MySQL报错1093 - You can't specify target table 't' for update in FROM clause, Time: 0

约定不等于承诺〃 2023-07-08 09:26 2阅读 0赞

报错内容：

1093 - You can't specify target table 't' for update in FROM clause, Time: 0

前情提示：

Mac OS10.14+MySQL8.0.18；

centOS6.8final+MySQL5.6；

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNzA4NzYz_size_16_color_FFFFFF_t_70][]

解决：

方法一：多嵌套一层。多来一层子查询

以上SQL修改如下：

UPDATE trace_videorecord SET VideoAddress = 'h8' WHERE VideoID IN
    (
    SELECT * FROM (
    SELECT mg.VideoID FROM sale_goods m LEFT JOIN  prod_task md ON m.TaskID = md.TaskID  LEFT JOIN prod_area ma on md.AreaID = ma.AreaID LEFT JOIN acct_factory mb ON ma.FactoryID = mb.FactoryID  LEFT JOIN trace_videorecord mg ON ma.AreaID = mg.AreaID WHERE m.GoodsID = '128' and mg.Status=1 ORDER BY mg.AddTime DESC LIMIT 0,30
    )a
    )

方法二：中间表，临时表。

1.创建一张临时表，将要删除的条件自动存入临时表中：
    2.再根据临时表，删除主表数据：
    3.最后删除掉临时表：
    create table tmp as select min(id) as a from user group by additive;
    delete from user where id not in (select a from tmp);
    drop table tmp;

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNzA4NzYz_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200226101547898.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMxNzA4NzYz,size_16,color_FFFFFF,t_70