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)
转自http://www.cnblogs.com/lteal/archive/2013/05/06/3062709.html
第一步：对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);
