You can’t specify target table ‘xxx’ for update in FROM clause的解决
https://www.cnblogs.com/whowhere/p/5666814.html
1、update 时,更新的表不能在set和where中用于子查询;
2、update 时,可以对多个表进行更新(sqlserver不行);
如:update ta a,tb b set a.Bid=b.id ,b.Aid=a.id;
3、update 后面可以做任意的查询,这个作用等同于from;
运行成功
SELECT * FROM wygl_park
WHERE park_id
IN (
SELECT park_id
FROM wygl_park T1
WHERE user_phone=110
)
运行失败
update wygl_park T1,
(
SELECT park_id
FROM wygl_park T1
WHERE user_phone=110
) T2
set T1.user_name =null,
T1.user_phone =null
WHERE T1.park_id=T2.park_id
进行修改
<update id="removeOwnerParkIds" parameterType="java.util.List">
<foreach collection="list" item="item" index="index" open="" close="" separator=";">
update wygl_park T1,
(
SELECT park_id
FROM wygl_park T1
WHERE user_phone=${item}
) T2
<trim prefix="SET" suffixOverrides=",">
user_name =null,
user_phone =null,
</trim>
WHERE T1.park_id=T2.park_id
</foreach>
</update>