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')

 

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