1093 - You can't specify target table 'pj_grpjb' for update in FROM clause, Time: 0.079000s
时间: 2024-03-08 21:42:13 浏览: 26
以下是解决1093错误的方法:
1. 使用临时表进行查询结果转化
```sql
UPDATE go_order_pj
SET project_code = '02'
WHERE go_order_pj_id IN
(
SELECT * FROM
(
SELECT go_order_pj_id
FROM go_order_pj
WHERE go_order_id IN
(
SELECT go_order_id
FROM go_order
WHERE org_id = '6482277243980976128'
)
) AS temp_table
);
```
2. 使用连接(JOIN)进行更新
```sql
UPDATE go_order_pj
JOIN go_order ON go_order_pj.go_order_id = go_order.go_order_id
SET go_order_pj.project_code = '02'
WHERE go_order.org_id = '6482277243980976128';
```
相关问题
1093 - You can't specify target table 'asks_over_time' for update in FROM clause
这个错误是因为 MySQL 不允许在 UPDATE 语句中直接使用被更新的表(这里是 asks_over_time)作为子查询的源表,否则会导致死锁的风险。因此,需要对该 SQL 语句进行改写。
一种常见的方法是使用一个临时表来保存要更新的记录的主键,然后在 UPDATE 语句中引用这个临时表。具体来说,可以将子查询中的结果保存到一个临时表中,然后在 UPDATE 语句中引用这个临时表来更新 asks_over_time 表。改写后的 SQL 语句如下:
```
CREATE TEMPORARY TABLE temp_table
SELECT t1.ask_id
FROM asks t1
INNER JOIN asks_over_time t2 ON t1.ask_id = t2.ask_id
WHERE t2.is_accepted = 1 AND t1.handle_time IS NOT NULL
AND t1.handle_time != '' AND t1.handle_time >= t2.create_time;
UPDATE asks_over_time
SET is_replied = 1
WHERE ask_id IN (SELECT ask_id FROM temp_table);
DROP TEMPORARY TABLE IF EXISTS temp_table;
```
这样就可以避免出现 "You can't specify target table 'asks_over_time' for update in FROM clause" 的错误。同时,使用临时表可以提高 SQL 语句的执行效率。
Error Code: 1093. You can't specify target table 'EMP' for update in FROM clause
Error Code: 1093 is a MySQL error that occurs when you try to update a table that is also used in the subquery of the update statement. Specifically, the error message "You can't specify target table 'EMP' for update in FROM clause" indicates that you are trying to update the 'EMP' table, but the same table is also referenced in the subquery.
To resolve this issue, you can use a workaround by creating a temporary table or using a nested subquery. Here's an example of how you can rewrite your query to avoid this error:
```
UPDATE EMP
SET column1 = value1
WHERE id IN (
SELECT * FROM (
SELECT id
FROM EMP
WHERE condition
) AS temp
);
```
In this example, we create a temporary table 'temp' using a nested subquery and then use it in the main update statement. This way, we avoid referencing the same table 'EMP' in the subquery.
相关推荐
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)