错误提示:You can't specify target table 'XXX' for update in FROM clause
不能从子句中指定更新表的目标表“XXX”
原sql语句:
delete tabel1,tabel2 from info right join tabel1 on tabel2.id=tabel1.id where tabel2 in(select tabel2.id from tabel2 where tabel2.column like '${XXX}%')
这里的问题就是不能同时select同一表的中的数据再进行update该表
所以解决方法就是通过中间表进行更新,即通过查询后的数据生成表t,再引用表t的数据进行更新t2
delete t1,t2 from tabel1 as t1 right join tabel2 as t2 on t1.id=t2.id where t2.id in (select t.tid from( select tabel2.id as tid from tabel2 where tabel2.column like '${XXX}%') t)