My Sql -You can‘t specify target table ‘r‘ for update in FROM clause

发生异常语句：

update table1 r
inner join table2 s on r.sId =s.Id
set r.amount=s.Amount
WHERE r.sId in (
select sId from table1 where  sId in
(select sId from table1 group by sId HAVING COUNT(1)=1) and Type=3
) 

原因：

不能先直接select出同一表中的某些值，再update这个表(在同一语句中)，即不能依据某些字段值做判断再来更新某些字段的值，需要通过中间表的形式筛选某些值，再来更新表的数据。

如上语句是直接通过table1表来赛选值sId作为where的条件值

select sId from table1 where  sId in
(select sId from table1 group by sId HAVING COUNT(1)=1) and Type=3

解决：

增加中间表t来筛选sId

update table1 r
inner join table2 s on r.sId =s.Id
set r.amount=s.Amount
WHERE r.sId in (
select t.sId from(
select sId from table1 where  sId in
(select sId from table1 group by sId HAVING COUNT(1)=1) and Type=3
) t)