您好,登錄后才能下訂單哦!
這篇文章主要講解了“MySQL報(bào)錯(cuò):You can‘t specify target table ‘region‘ for update in FROM clause如何解決”,文中的講解內(nèi)容簡(jiǎn)單清晰,易于學(xué)習(xí)與理解,下面請(qǐng)大家跟著小編的思路慢慢深入,一起來研究和學(xué)習(xí)“MySQL報(bào)錯(cuò):You can‘t specify target table ‘region‘ for update in FROM clause如何解決”吧!
首先明確一點(diǎn)這個(gè)錯(cuò)誤只會(huì)發(fā)生在delete
語句或者update
語句,拿update來舉例 : update A表 set A列 = (select B列 from A表);
這種寫法就會(huì)報(bào)這個(gè)錯(cuò)誤,原因:你又要修改A表,然后又要從A表查數(shù)據(jù),而且還是同層級(jí)。Mysql就會(huì)認(rèn)為是語法錯(cuò)誤!
嵌套一層就可以解決,update A表 set A列 = (select a.B列 from (select * from A表) a);
當(dāng)然這個(gè)只是個(gè)示例,這個(gè)示例也存在一定的問題,比如(select a.B列 from (select * from A表) a)
他會(huì)查出來多條,然后賦值的時(shí)候會(huì)報(bào) 1242 - Subquery returns more than 1 row
。
嵌套一層他就可以和update撇清關(guān)系,會(huì)優(yōu)先查括號(hào)里面的內(nèi)容,查詢結(jié)果出來過后會(huì)給存起來,類似臨時(shí)表,可能有的人該好奇了,update A表 set A列 = (select B列 from A表);
我明明加括號(hào)了呀,難道不算嵌套嗎,當(dāng)然不算,那個(gè)括號(hào)根本沒有解決他們之間的層次關(guān)系!
以這張表為例:
DROP TABLE IF EXISTS `region`; CREATE TABLE `region` ( `Id` int(11) NOT NULL COMMENT '主鍵id', `Name` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '名稱', `Pid` int(11) NULL DEFAULT NULL COMMENT '父類id', `status` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '1' COMMENT '1:啟用,2:禁用', PRIMARY KEY (`Id`) USING BTREE, INDEX `FK_CHINA_REFERENCE_CHINA`(`Pid`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '省市區(qū)表' ROW_FORMAT = DYNAMIC;
錯(cuò)誤用法:
這個(gè)例子就是典型,我要修改
region
表數(shù)據(jù),要將pid
賦值為region
當(dāng)中的某一列數(shù)據(jù),這種寫法就會(huì)報(bào)錯(cuò)!mysql不支持同一張表當(dāng)中 既要修改又要查詢
。
UPDATE region SET pid = (select pid FROM region WHERE NAME = '市轄區(qū)') where name='北京';
明確一點(diǎn),這種的不是同一張表是不會(huì)報(bào)錯(cuò)的!
UPDATE region SET pid = (select id FROM banner) ;
正確用法:
UPDATE region SET pid = ( SELECT a.pid FROM ( SELECT Pid FROM region WHERE NAME = '市轄區(qū)' ) a ) WHERE NAME = '北京';
錯(cuò)誤用法:
UPDATE region a SET Name = '1' WHERE a.pid IN ( SELECT id FROM region WHERE NAME = '市轄區(qū)' );
正確用法:
UPDATE region a SET Name = '1' WHERE a.pid IN ( SELECT b.id FROM (select * FROM region) b WHERE b.NAME = '市轄區(qū)' );
這個(gè)sql沒有錯(cuò)誤示例,只有正確示例,相對(duì)來說比較復(fù)雜點(diǎn)!
UPDATE region a SET pid = ( SELECT b.pid FROM ( SELECT id, pid FROM region b WHERE b.NAME = '市轄區(qū)' ) b WHERE a.Pid = b.id ) WHERE a.pid IN ( SELECT c.id FROM ( SELECT * FROM region ) c WHERE c.NAME = '市轄區(qū)' );
再來看一個(gè)刪除的示例
錯(cuò)誤用法:
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 );
正確用法:
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 ) a );
(select...)
一定要加個(gè)別名,例如:(select...) a
否則報(bào)錯(cuò)如下:
感謝各位的閱讀,以上就是“MySQL報(bào)錯(cuò):You can‘t specify target table ‘region‘ for update in FROM clause如何解決”的內(nèi)容了,經(jīng)過本文的學(xué)習(xí)后,相信大家對(duì)MySQL報(bào)錯(cuò):You can‘t specify target table ‘region‘ for update in FROM clause如何解決這一問題有了更深刻的體會(huì),具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是億速云,小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章,歡迎關(guān)注!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。