delete 語句帶別名問題.
mysql 8.0 版本:
delete from table1 s
where project_id = ?
and not exists (select project_id from table2 u
where u.project_id = s.project_id
and s.delivery_date = u.use_date)
這個sql 執行一點問題都沒有.
這個sql 在 5.6 或者5.7 上跑是有問題的.
報錯:
org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback;
bad SQL grammar [delete from table1 s
where project_id = ? and not exists (select project_id from table2 u
where u.project_id = s.project_id
and s.delivery_date = u.use_date)];
nested exception is java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's where project_id = 330 and not exists (select project_id from ijk_project_uplo' at line 1
報錯了. 找不到 s 表.
這個是解析器的一個曲缺陷. 丟掉了 meta資訊 ,不知道 s 是table1 的別名.
修改方法:
1)
delete s from table1 s
where project_id = ?
and not exists (select project_id from table2 u
where u.project_id = s.project_id
and s.delivery_date = u.use_date)
2) 直接去掉 s 子查詢 用table1
delete from table1
where project_id = ?
and not exists (select project_id from table2 u
where u.project_id =table1.project_id
and table1.delivery_date = u.use_date)
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/133735/viewspace-2848072/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- MySQL DELETE 表別名問題MySqldelete
- MySQL 中 DELETE 語句中可以使用別名麼?MySqldelete
- 【SQL】11 SQL DELETE 語句SQLdelete
- Sql Server系列:Delete語句SQLServerdelete
- FORALL執行DELETE語句delete
- jivejdon sql語句問題SQL
- Sqlserver的merge into或delete語句堵塞select語句,鎖型別是LCK_M_ISSQLServerdelete型別
- WorkBench,DELETE 標準語句失敗delete
- 【SQL Server學習筆記】Delete 語句、Output 子句、Merge語句SQLServer筆記delete
- MySQL不支援DELETE使用表別名?MySqldelete
- SQLite語句(二):INSERT DELETE UPDATE SELECTSQLitedelete
- delete語句對索引的影響之分析delete索引
- 用merge 語句代替 insert 和deletedelete
- Oracle 指定別名"A" or"a"造成的問題Oracle
- SQL語句巢狀查詢問題SQL巢狀
- mysql常用語句及問題處理MySql
- 對sql語句的優化問題SQL優化
- oracle效能問題:sql語句優化OracleSQL優化
- 解決了帶javascript語句的連結全都打不開的問題(轉)JavaScript
- mssql sqlserver update delete表別名用法簡介SQLServerdelete
- 比CRUD多一點兒(三):UPDATE、DELETE語句delete
- LINQ to SQL語句之Insert/Update/Delete操作SQLdelete
- 一個關於c++字串處理和delete[]與delete差別的問題 (轉)C++字串delete
- 50個SQL語句(MySQL版) 問題十四MySql
- 兩個看似奇怪的MySQL語句問題MySql
- 對sql語句的最佳化問題SQL
- SQL 語句select top 變數問題SQL變數
- KunlunDB功能之insert/update/delete...returning語句delete
- 恢復update,delete表資料錯誤的語句delete
- 幽默:演算法只是一堆帶有花哨名稱的 IF ELSE 語句演算法
- UTF-8 帶簽名和不帶簽名的區別
- 生產SQL語句突然變慢問題定位SQL
- mysql insert語句錯誤問題解決MySql
- SQL查詢語句臃腫問題淺析SQL
- 關於sql語句的遊標共享問題SQL
- 執行oracle DDL語句要注意的問題Oracle
- 一個JTextPane寫SQL語句的問題SQL
- 輕量ORM-SqlRepoEx (四)INSERT、UPDATE、DELETE 語句ORMSQLdelete