mysql 不能多层嵌套_mysql 不支持嵌套更新的有关问题

执行嵌套更新，遇到错误You can't specify target table 'scar' for update in FROM clause

update scar

set main_resource_id=(

select id from  scar2

where 1=1 and scar2.subject_templates_activity_resource_id=(

select main_resource_id from  star

where 1=1 and star.id=scar.subject_templates_activity_resource_id

)

)

where 1=1

and scar.group_type=2

查询发现mysql不支持嵌套更新，只好改用表连接来进行更新

update  scar

inner join scar2

inner join  star

set scar.main_resource_id=scar2.id

where 1=1

and scar2.subject_templates_activity_resource_id=star.main_resource_id

and star.id=scar.subject_templates_activity_resource_id

and scar.group_type=2

and scar.activity_id in (select id from rsc_subject_cases_activity sca where sca.subject_cases_id='1')

感觉这样性能会查一些，不过等后续修改的时候再查执行计划看看