MySQL update where报错 You can't specify target table '表名' for update in FROM clause

2021-01-08

148

2021-01-08 14:34:07

原创

<h1 id="前言">前言</h1>
<p>使用MySQL更新数据的时候，报错<code>You can't specify target table '表名' for update in FROM clause</code>，原因是在一条语句中，不能<code>select</code>出同一张表中的某些字段作为条件，再去<code>update</code>该表的字段。</p>
<h1 id="sql">SQL</h1>
<p>以下SQL报错：</p>
<pre><code>update t_room
set fexpand_id = null, fexpand_org_id = null, fexpand_position_id = null, fexpand_time = null
where fid in(
	select
		r.fid
	from t_room r
	left join t_erp_person p on p.fid = r.fexpand_id
	where p.fposition_id = 'xxx'
	and r.fabandon_status = 'ENABLED' and r.froom_status = 'RENT'
);
</code></pre>
<p>错误信息：
<code>[HY000][1093] You can't specify target table 't_room' for update in FROM clause</code></p>
<p>这种情况，把where条件中的数据集包装一下就好了，当中间表来用。</p>
<pre><code>update t_room
set fexpand_id = null, fexpand_org_id = null, fexpand_position_id = null, fexpand_time = null
where fid in(
	select a.fid from (
	   select r.fid from t_room r
            left join t_erp_person p on p.fid = r.fexpand_id
            where p.fposition_id = 'xxx'
            and r.fabandon_status = 'ENABLED' and r.froom_status = 'RENT'
       ) a
);
</code></pre>
<p>OK，执行成功！！！</p>

MySQL

MySQL update where报错 You can't specify target table '表名' for update in FROM clause

评论