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 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 ... ) 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.
其实想想也是这样的，对同⼀张表查的同时更新会引起数据不⼀致的问题吧，但是将查询结果事先放到临时表中就不会有这个问题了。
export软件什么意思
这个是我们在使⽤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 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 use
中⽂意思：不能先select出同⼀表中的某些值，再update这个表(在同⼀语句中)
bagging boosting stacking中文转ascii码怎么算解决： 注：把同⼀个table重新包⼀遍，重命名
原句
updateperson_level_testsetcurrentStatus=0whereid=(selectmax(m.id)fromperson_level_test)
修改后
updateperson_level_testsetcurrentStatus=0whereid=
(selectmax(m.id)from(select*fromperson_level_test) m)
第⼀步：对a pto p表进⾏操作
操作没有问题，按照本思路对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
mysql语句转oracle三、如上，修改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
delete from ecs_shipping where id in (select a.id from ( select max(id) as id from ecs_shipping as b
where
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
vba读取当前文件夹里多个文件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);
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论