mysql中You can’t specify target table for update in FROM cl
mysql中You can't specify target table for update in FROM clause錯誤的意思是說,不能先select出同一表中的某些值,再update這個表(在同一語句中)。 例如下面這個sql:
程式碼如下:
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出的結果再透過中間表select一遍,這樣就規避了錯誤。注意,這個問題只出現於mysql, mssql和oracle不會出現此問題。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/69994010/viewspace-2753481/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- You can‘t specify target table ‘Person‘ for update in FROM clause
- Mysql update in報錯 [Err] 1093 - You can't specify target table 'company_info' for update in FROM clauseMySql
- rman: can't open target
- [ERROR] Can't open the mysql.plugin tableErrorMySqlPlugin
- MySQL報錯Table 'plugin' is read only [ERROR] Can't open the mysql.plugin table.MySqlPluginError
- Table is specified twice, both as a target for 'UPDATE' and as a separate source
- Mongoose can't update timestampGo
- RMAN的"rman: can't open target"錯誤
- MySQL 建立外來鍵報錯Can't write; duplicate key in tableMySql
- Mount: you must specify the filesystem type
- 【MySQL】ERROR 1093 You canMySqlError
- MYSQL報1022錯誤:Can't write;duplicate key in table '.....'MySql
- 【MySQL】ERROR 1005 (HY000): Can't create table' (errno: 150)MySqlError
- Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist解決辦法ErrorMySql
- I can't truly give chinese sex pills you a conclusive remedyREM
- 2008 5 19: I can't make exception for youException
- mysql啟動時報錯Can't read from messagefile errmsg.sysMySql
- linux上“rman can't open target”問題解決Linux
- mysql遇到Variable can’t be set to the value of ‘NULL’MySqlNull
- mysql can't start dues to the disk space is fullMySql
- 關於oracle 10G for suse 9 的rman: can't open targetOracle 10g
- 關於 mysql 中的 select * from table_a,table_b 的問題MySql
- MysqL中的Show Index From Table_Name命令說明MySqlIndex
- mysql [ERROR] Can't create IP socket: Permission deniedMySqlError
- linux 掛載光碟:mount: you must specify the filesystem typeLinux
- 'mysql.column_stats' doesn't exist and Table 'mysql.index_stats' doesn't existMySqlIndex
- MySQL連線提示Can't connect to local MySQL server through socketMySqlServer
- Can't connect to local MySQL server through socket '/tmp/mysql.sock'MySqlServer
- 【MySQL】Can't get stat of './mysql/slow_log.CSV' (Errcode: 2)MySql
- 【MySQL】select for update 的Row Lock 與Table LockMySql
- RMAN備份、恢復實驗室 之 備份篇 【rman: can't open target】
- Laravel Lego :Save you from CRUDLaravelGo
- 解決Error (1133): Can’t find any matching row in the user tableError
- Mysql報錯Fatal error:Can't open and lock privilege tablesMySqlError
- MySQL不能啟動 Can't start server : Bind on unix sockeMySqlServer
- mysql can't connect error about privilege----not root userMySqlError
- Oracle模擬MySQL的show index from table命令OracleMySqlIndex
- MySQL 啟動報錯 Table 'mysql.plugin' doesn't existMySqlPlugin