解决——》[Err] 1093 - You can't specify target table 'fcf' for update in FROM clause

阳光穿透心脏的1/2处 2023-12-12 06:19 117阅读 0赞

版权声明：本文为博主原创文章，无需授权即可转载，甚至无需保留以上版权声明，转载时请务必注明作者。  
[https://blog.csdn.net/weixin\_43453386/article/details/89154811][https_blog.csdn.net_weixin_43453386_article_details_89154811]

#### 解决——》\[Err\] 1093 - You can't specify target table 'fcf' for update in FROM clause ####

*   *  1、操作
     *  2、现象（错误信息）
     *  3、原因
     *  4、解决
     *   *  通过中间表再次select

table ‘fcf’ for update in FROM clause)

### 1、操作 ###

数据库更新

### 2、现象（错误信息） ###

UPDATE house_hub_fence.tb_fence_community_fence fcf
    SET fcf.deleted = 1
    WHERE
    	fcf.community_id IN (
    		SELECT
    			f.community_id
    		FROM
    			house_hub_fence.tb_fence_community_fence f
    		LEFT JOIN house_asset.community hc ON f.community_id = hc.community_id
    		WHERE
    			hc.community_id IS NULL
    		OR hc.community_id = ''
    	)

`[Err] 1093 - You can't specify target table 'fcf' for update in FROM clause`

### 3、原因 ###

分析sql的现象：在更新这个表数据时又查询了它，而查询的数据又做了更新的条件  
具体原因：MySQL在update的时候，原始表不能出现在where 后面第一层的子查询当中

### 4、解决 ###

#### 通过中间表再次select ####

UPDATE house_hub_fence.tb_fence_community_fence fcf
    SET fcf.deleted = 1
    WHERE
    	fcf.community_id IN (
    		SELECT
    			tmp.community_id
    		FROM
    			(
    				SELECT
    					f.community_id
    				FROM
    					house_hub_fence.tb_fence_community_fence f
    				LEFT JOIN house_asset.community hc ON f.community_id = hc.community_id
    				WHERE
    					hc.community_id IS NULL
    				OR hc.community_id = ''
    			) tmp
    	)

[https_blog.csdn.net_weixin_43453386_article_details_89154811]: https://blog.csdn.net/weixin_43453386/article/details/89154811