MYSQL错误代码：
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 clau
意思是不能在同⼀语句中更新lect出的同⼀张表元组的属性值
解决⽅法：将lect出的结果通过中间表再lect⼀遍即可。
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 lect 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 clau. Example:
UPDATE t ... WHERE col = (SELECT * FROM (SELECT ... ) AS _t ...);
Here the result from the subquery in theFROMclau is stored as a temporary table, so the relevant rows int have already been lected by the time the update tottakes place.
其实想想也是这样的，对同⼀张表查的同时更新会引起数据不⼀致的问题吧，但是将查询结果事先放到临时表中就不会有这个问题了。
这个是我们在使⽤update或者delete语句时，在where条件⾥⾯加⼊的⼦查询导致的。例如如下的update语句：update table t type = 'static' where id in (
lect id from ws_product where first_name ='superman'
);
医用制氧机十大品牌修改上述语句为下⾯这样，该问题可以解决：
update ws_product t type = 'static' where id in (
lect id form (
lect 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 Yo u c a n’t spec ify ta rget ta ble ‘ta ble na me’ fo r upda te in FR OM c la u
中⽂意思：不能先lect出同⼀表中的某些值，再update这个表(在同⼀语句中)
解决： 注：把同⼀个table重新包⼀遍，重命名
原句
updateperson_level_testtcurrentStatus=0whereid=(lectmax(m.id)fromperson_level_test)
修改后
updateperson_level_testtcurrentStatus=0whereid=
(lectmax(m.id)from(lect*fromperson_level_test) m)word如何设置页码
第⼀步：对a pto p表进⾏操作
操作没有问题，按照本思路对product表进⾏操作
delete from tbl where id in (lect max(id) from tbl a where EXISTS ( lect 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 clau错误
3、解决思路：既然Mysql不让对查询到的⽬标语句进⾏更新，那么我在它的上⾯在套⼀个⼦查询就可以。
将SELECT出的结果再通过中间表SELECT⼀遍，这样就规避了错误。需要注意的是，这个问题只出现于MySQL，MSSQL和Oracle不会出现此问题。
delete from tbl where id in (lect a.id from (lect max(id) id from tbl a where EXISTS (lect 1 from tbl b where a.tac=b.tac group by tac HAVING count(1)>1)group by tac) as a)
山羊怎么画
update message t content='Hello World' where id in( lect min_id from ( lect 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的全部记录
lect * from ecs_order_shipping where type = 11;
#分组后，记录少了，说明有重复的记录
lect * from ecs_order_shipping where type = 11 group by order_sn;打开的书怎么画
#这样筛选出那些重复的订单
lect * from ecs_order_shipping where type = 11 group by order_sn having count(*) >1;
#从筛选的重复订单中，得到id最⼤的
lect 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 (lect 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 clau
三、如上，修改sql语句如下即可
/****每个订单type等于11，12的记录，出现了很多重复的，要删除重复项，只留下最早的那个***/
delete from ecs_shipping where id in (lect a.id from (lect max(id) as id from ecs_shipping as b where
delete from ecs_shipping where id in (lect a.id from ( lect max(id) as id from ecs_shipping as b
where
mysql>delete from t where id in (lect id from t where id < 5);
塑料羽毛球ERROR1093 (HY000): You can't specify target table 't' for update in FROM clau
改为下⾯就OK
delete from t where id in(lect * from ((lect id from t where id <5) tmp );
主句(lect * from (从句 temp)
蛋白高
删除重复，但保留最⼩id项。
DELETE FROM file WHERE fileaddress IN (lect * from ((SELECT fileaddress FROM file GROUP BY fileaddress HAVING count(fileaddress) > 1) a) )
AND id NOT IN (lect * 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);
