MYSQL错误代码： 1093 You can‘t specify target table ‘sc‘ for update in FROM clause

MYSQL执行如下语句报错：

UPDATE sc SET grade =grade*1.05 WHERE grade < (SELECT AVG(grade) AS avg_grade FROM sc) 

报错信息如下：

错误代码： 1093

You can't specify target table 'sc' for update in FROM clause

意思是不能在同一语句中更新select出的同一张表元组的属性值

解决方法：将select出的结果通过中间表再select一遍即可。

UPDATE sc SET grade =grade*1.05 WHERE grade < (SELECT avg_grade FROM (SELECT AVG(grade) AS avg_grade FROM sc) AS temp)

MYSQL手册restrictions.html#subquery-restrictions里给出了限制规则和解决方法：

In general, you cannot modify a table and select from the same table in a subquery. For example, this limitation applies to statements of the following forms:

DELETE FROM t WHERE ... (SELECT ... FROM t ...);

UPDATE t ... WHERE col = (SELECT ... FROM t ...);

{INSERT|REPLACE} INTO t (SELECT ... FROM t ...);

Exception: The preceding prohibition does not apply if you are using a subquery for the modified table in theFROM clause. Example:

UPDATE t ... WHERE col = (SELECT * FROM (SELECT ... FROM t...) AS _t ...);

Here the result from the subquery in theFROMclause is stored as a temporary table, so the relevant rows int have already been selected by the time the update tottakes place.

其实想想也是这样的，对同一张表查的同时更新会引起数据不一致的问题吧，但是将查询结果事先放到临时表中就不会有这个问题了。

---

这个是我们在使用update或者delete语句时，在where条件里面加入的子查询导致的。例如如下的update语句：

update table set type = 'static' where id in (

select id from ws_product where first_name ='superman'

);

修改上述语句为下面这样，该问题可以解决：

update ws_product set type = 'static' where id in (

select id form (

 select id from ws_product where first_name ='superman'

) xx

);

注意，这样一定要给最里面的子查询定义一个别名，不然会报另外一个错误：

自己的例子：下面这种方式报错   error code：1093

DELETE FROM Persons WHERE id NOT IN (SELECT MAX(id) FROM Persons GROUP BY NAME,age)

所以改成：

DELETE FROM Persons WHERE id NOT IN (SELECT * FROM (SELECT MAX(id) FROM Persons GROUP BY NAME,age) temp);

原因：一定要给最里面的子查询定义一个别名.

---

错误 ：1093 You can’t specify target table ‘table name’ for update in FROM clause 

中文意思：不能先select出同一表中的某些值，再update这个表(在同一语句中)

解决： 注:把同一个table重新包一遍，重命名

原句

updateperson_level_testsetcurrentStatus=0whereid=(selectmax(m.id)fromperson_level_test)

修改后

updateperson_level_testsetcurrentStatus=0whereid=(selectmax(m.id)from(select*fromperson_level_test) m)

转自错误 1093 You can't specify target table 'table name' for update in FROM clause - 蓝海豹 - 博客园

---

第一步：对aptop表进行操作

---

操作没有问题，按照本思路对product表进行操作

delete from tbl where id in (select max(id) from tbl a where EXISTS ( select 1 from tbl b where a.tac=b.tac group by tac HAVING count(1)>1) group by tac)

产生You can't specify target table '表名' for update in FROM clause错误

3、解决思路：既然Mysql不让对查询到的目标语句进行更新，那么我在它的上面在套一个子查询就可以。

将SELECT出的结果再通过中间表SELECT一遍，这样就规避了错误。需要注意的是，这个问题只出现于MySQL，MSSQL和Oracle不会出现此问题。

delete from tbl where id in (select a.id from (select max(id) id from tbl a where EXISTS (select 1 from tbl b where a.tac=b.tac group by tac HAVING count(1)>1)group by tac) as a)

update message set content='Hello World' where id in( select min_id from ( select min(id) as min_id from message group by uid) as a );

sql = "DELETE FROM qishu_books_sort08 WHERE Novel_url IN(SELECT * FROM(SELECT Novel_url FROM qishu_books_sort08 GROUP BY Novel_url HAVING count(Novel_url)>1 ORDER BY Novel_ID ASC) as tmp) AND Novel_ID NOT IN(SELECT * FROM(SELECT min(Novel_ID) FROM qishu_books_sort08 GROUP BY Novel_url HAVING count(Novel_url)>1 ORDER BY Novel_ID ASC) as tmp);" 

---

如下业务场景，ecs_order_shipping表里面记录了每一个订单的配送流转记录，type从11，12，21，22，23这么递进.

按理说每个订单的每一个type就出现一次，因为系统bug造成type为11、12的记录，都出现了两次或多次。对于每一个订单，如果它有两条type等于11的ecs_order_shipping记录，那么只保留第一条，其他的删除。

一、先查出来那些要被删除的记录的id组合

#查出type为11的全部记录

select * from ecs_order_shipping where type = 11;

#分组后，记录少了，说明有重复的记录

select * from ecs_order_shipping where type = 11 group by order_sn;

#这样筛选出那些重复的订单

select * from ecs_order_shipping where type = 11 group by order_sn  having count(*) >1;

#从筛选的重复订单中，得到id最大的

select max(id) from ecs_order_shipping where type=11 group by order_sn having count(*) >1;

二、根据这些id组合，删除他们

delete from ecs_order_shipping where id in (select max(id) from ecs_order_shipping where type=11 group by order_sn having count(*) >1)

这样就会报错！！！Error : You can't specify target table 'ecs_order_shipping' for update in FROM clause

三、如上，修改sql语句如下即可

/****每个订单type等于11，12的记录，出现了很多重复的，要删除重复项，只留下最早的那个***/

delete from ecs_shipping where id in (select a.id from (select max(id) as id from ecs_shipping  as b where b.type=11  group by b.order_sn HAVING count(*)>1) as a );

delete from ecs_shipping where id in (select a.id from ( select max(id) as id from ecs_shipping  as b where b.type=12 group by b.order_sn HAVING count(*)>1)as a);

---

mysql>delete from t where id in (select id from t where id < 5);  

ERROR1093 (HY000): You can't specify target table 't' for update in FROM clause

改为下面就OK

delete from t where id in(select * from ((select id from t where id <5) tmp );  

主句(select * from (从句 temp)

删除重复，但保留最小id项。

 DELETE FROM file WHERE  fileaddress IN (select * from ((SELECT  fileaddress FROM  file GROUP BY fileaddress HAVING count(fileaddress) > 1) a) )

AND id NOT IN (select * from ((SELECT min(id) FROM  file GROUP BY  fileaddress  HAVING  count(fileaddress) > 1   ) b))   

---

删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录

DELETE  FROM  people WHERE peopleName IN (SELECT peopleName FROM  people  GROUPBY  peopleName HAVING count (peopleName)>1)AND peopleId NOT IN ( SELECT min (peopleId) FROM  people GROUPBY  peopleName HAVING count (peopleName)>1);