MYSQL執(zhí)行如下語(yǔ)句報(bào)錯(cuò)：

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

報(bào)錯(cuò)信息如下：

錯(cuò)誤代碼： 1093

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

意思是不能在同一語(yǔ)句中更新select出的同一張表元組的屬性值

解決方法：將select出的結(jié)果通過(guò)中間表再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手冊(cè)restrictions.html#subquery-restrictions里給出了限制規(guī)則和解決方法：

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.

其實(shí)想想也是這樣的粘我，對(duì)同一張表查的同時(shí)更新會(huì)引起數(shù)據(jù)不一致的問(wèn)題吧雹嗦，但是將查詢結(jié)果事先放到臨時(shí)表中就不會(huì)有這個(gè)問(wèn)題了。

這個(gè)是我們?cè)谑褂胾pdate或者delete語(yǔ)句時(shí)，在where條件里面加入的子查詢導(dǎo)致的沾鳄。例如如下的update語(yǔ)句：

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

select id from ws_product where first_name ='superman'

);

修改上述語(yǔ)句為下面這樣踪宠，該問(wèn)題可以解決：

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

select id form (

?select id from ws_product where first_name ='superman'

) xx

);

注意冲泥，這樣一定要給最里面的子查詢定義一個(gè)別名，不然會(huì)報(bào)另外一個(gè)錯(cuò)誤：

自己的例子：下面這種方式報(bào)錯(cuò) ? 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);

原因：一定要給最里面的子查詢定義一個(gè)別名.

錯(cuò)誤 ：1093 You can’t specify target table ‘table name’ for update in FROM clause?

中文意思：不能先select出同一表中的某些值怀伦，再u(mài)pdate這個(gè)表(在同一語(yǔ)句中)

解決： 注:把同一個(gè)table重新包一遍脆烟，重命名

原句

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

修改后

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

轉(zhuǎn)自http://www.cnblogs.com/lteal/archive/2013/05/06/3062709.html

第一步：對(duì)aptop表進(jìn)行操作

操作沒(méi)有問(wèn)題，按照本思路對(duì)product表進(jìn)行操作

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)

產(chǎn)生You can't specify target table '表名' for update in FROM clause錯(cuò)誤

3房待、解決思路：既然Mysql不讓對(duì)查詢到的目標(biāo)語(yǔ)句進(jìn)行更新邢羔，那么我在它的上面在套一個(gè)子查詢就可以。

將SELECT出的結(jié)果再通過(guò)中間表SELECT一遍吴攒，這樣就規(guī)避了錯(cuò)誤张抄。需要注意的是，這個(gè)問(wèn)題只出現(xiàn)于MySQL洼怔，MSSQL和Oracle不會(huì)出現(xiàn)此問(wèn)題署惯。

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);"?

如下業(yè)務(wù)場(chǎng)景，ecs_order_shipping表里面記錄了每一個(gè)訂單的配送流轉(zhuǎn)記錄镣隶，type從11极谊，12，21安岂，22轻猖，23這么遞進(jìn).

按理說(shuō)每個(gè)訂單的每一個(gè)type就出現(xiàn)一次，因?yàn)橄到y(tǒng)bug造成type為11域那、12的記錄咙边，都出現(xiàn)了兩次或多次。對(duì)于每一個(gè)訂單，如果它有兩條type等于11的ecs_order_shipping記錄败许，那么只保留第一條王带，其他的刪除。

一市殷、先查出來(lái)那些要被刪除的記錄的id組合

#查出type為11的全部記錄

select * from ecs_order_shipping where type = 11;

#分組后愕撰，記錄少了，說(shuō)明有重復(fù)的記錄

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

#這樣篩選出那些重復(fù)的訂單

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

#從篩選的重復(fù)訂單中醋寝，得到id最大的

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

二搞挣、根據(jù)這些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)

這樣就會(huì)報(bào)錯(cuò)Ｒ粜摺４呀啊！Error : You can't specify target table 'ecs_order_shipping' for update in FROM clause

三黄选、如上蝇摸，修改sql語(yǔ)句如下即可

/****每個(gè)訂單type等于11，12的記錄办陷，出現(xiàn)了很多重復(fù)的貌夕，要?jiǎng)h除重復(fù)項(xiàng)，只留下最早的那個(gè)***/

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)

刪除重復(fù)民镜，但保留最小id項(xiàng)啡专。

?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))? ?

刪除表中多余的重復(fù)記錄，重復(fù)記錄是根據(jù)單個(gè)字段（peopleId）來(lái)判斷制圈，只留有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);