MySQL多嵌套SELECT转UPDATE报错1288，求解决方案

我编写了一条包含多层LEFT JOIN嵌套的SELECT语句，可正常查询获取结果。现在希望将其改写为UPDATE语句，用于修改project表的LEAD字段为'kami'（WHERE条件为project.ID='10000'），但执行时出现错误码1288：“The target table ww of the UPDATE is not updatable”。

原SELECT语句：

select T3.*,ao_00b950_aoproject_value.VALUE ODP from
(select T2.*,ao_00b950_aoproject_value.VALUE SOD from 
(select T1.*,ao_00b950_aoproject_value.VALUE Platform from (SELECT project.LEAD,project.id,project.pname,project.pkey,ao_00b950_aoproject_value.VALUE Status FROM jira820db.project
LEFT JOIN jira820db.ao_00b950_aoproject_value 
on project.ID=ao_00b950_aoproject_value.PROJECT_ID 
and ao_00b950_aoproject_value.FIELD_ID =1) as T1
left join ao_00b950_aoproject_value
on T1.ID = ao_00b950_aoproject_value.project_ID
and ao_00b950_aoproject_value.field_ID=26) as T2
left join ao_00b950_aoproject_value
on T2.ID = ao_00b950_aoproject_value.project_ID
and ao_00b950_aoproject_value.field_ID=28) as T3
left join ao_00b950_aoproject_value
on T3.ID = ao_00b950_aoproject_value.project_ID
and ao_00b950_aoproject_value.field_ID=27

改写的UPDATE语句：

update
(select T3.*,ao_00b950_aoproject_value.VALUE ODP from
(select T2.*,ao_00b950_aoproject_value.VALUE SOD from 
(select T1.*,ao_00b950_aoproject_value.VALUE Platform from (SELECT project.LEAD,project.id,project.pname,project.pkey,ao_00b950_aoproject_value.VALUE Status FROM jira820db.project
LEFT JOIN jira820db.ao_00b950_aoproject_value 
on project.ID=ao_00b950_aoproject_value.PROJECT_ID 
and ao_00b950_aoproject_value.FIELD_ID =1) as T1
left join ao_00b950_aoproject_value
on T1.ID = ao_00b950_aoproject_value.project_ID
and ao_00b950_aoproject_value.field_ID=26) as T2
left join ao_00b950_aoproject_value
on T2.ID = ao_00b950_aoproject_value.project_ID
and ao_00b950_aoproject_value.field_ID=28) as T3
left join ao_00b950_aoproject_value
on T3.ID = ao_00b950_aoproject_value.project_ID
and ao_00b950_aoproject_value.field_ID=27) as ww
set project.LEAD = 'kami'
where project.ID ='10000';

错误原因是MySQL不允许更新这种多层嵌套LEFT JOIN生成的派生表（这类表属于不可更新视图/派生表）。结合你的需求，提供两种解决方法：

方法一：直接更新目标表（最简方案） #

你的需求只是修改project表中指定ID的LEAD字段，完全不需要关联其他表，直接执行以下语句即可：

UPDATE jira820db.project
SET LEAD = 'kami'
WHERE ID = '10000';

方法二：使用UPDATE JOIN语法（适用于需要关联表过滤的场景） #

如果后续需要基于关联的ao_00b950_aoproject_value表添加过滤条件，可改用直接JOIN的写法，避免嵌套派生表：

UPDATE jira820db.project
LEFT JOIN jira820db.ao_00b950_aoproject_value v1 
  ON project.ID = v1.PROJECT_ID AND v1.FIELD_ID = 1
LEFT JOIN jira820db.ao_00b950_aoproject_value v2 
  ON project.ID = v2.PROJECT_ID AND v2.FIELD_ID = 26
LEFT JOIN jira820db.ao_00b950_aoproject_value v3 
  ON project.ID = v3.PROJECT_ID AND v3.FIELD_ID = 28
LEFT JOIN jira820db.ao_00b950_aoproject_value v4 
  ON project.ID = v4.PROJECT_ID AND v4.FIELD_ID = 27
SET project.LEAD = 'kami'
WHERE project.ID = '10000';

这种写法符合MySQL对可更新语句的要求，若需要根据关联表的字段过滤，只需在WHERE子句中添加对应条件即可。

内容的提问来源于stack exchange，提问作者Kamyar Sharifi