MYSQL 1093 报错解决方案

博主： zmx
发布时间：2023 年 09 月 20 日
506 次浏览
暂无评论
1113字数
分类： MySQL

# 自更新表数据

当执行一个简单的update语句时，发生了1093报错，语句没问题，逻辑也没问题，也很确定自己的代码不会引发数据错误，但是MySQL不允许这样执行

```bash
1093 - You can't specify target table 'TABLE_QWE' for update in FROM clause
```

# 报错语句

样例1：

```SQL
UPDATE TABLE_QWE
SET TCLOSE = RAND() * 10000 
WHERE
	N_ID IN( SELECT
	N_ID 
FROM
	`TABLE_QWE` QWE
	LEFT JOIN TABLE_ASD ASD ON ASD.id = QWE.id 
WHERE
	ASD.sid = 150);
```

> 1093 - You can't specify target table 'TABLE_QWE' for update in FROM clause

# 解决方案

```SQL
SET SQL_SAFE_UPDATES = 0;

UPDATE TABLE_QWE 
SET TCLOSE = RAND() * 10000 
WHERE
	N_ID IN( SELECT N_ID FROM (SELECT
	N_ID 
FROM
	`TABLE_QWE` QWE
	LEFT JOIN TABLE_ASD ASD ON ASD.id = QWE.id 
WHERE
	ASD.sid = 150)A);
	
SET SQL_SAFE_UPDATES = 1;
```

或者

```SQL
update TABLE_QWE join TABLE_ASD ON TABLE_QWE.id = TABLE_ASD.id SET TABLE_QWE.TCLOSE =  RAND() * 10000 WHERE  TABLE_ASD.sid = 150
```

# 删除操作

内部再嵌套一层即可，也有可能需要`SET SQL_SAFE_UPDATES = 0;`

```sql
DELETE FROM table WHERE col in (SELECT col FROM (SELECT b.col FROM table a
right join table b on a.col = b.col
where
a.col not null)T) ;
```