执行mysql update,或者delete的时候会遇到:
You can't specify target table for update in FROM clause
相关的原因自不必说:下面有stackoverflow中的帖子:
https://stackoverflow.com/questions/4429319/you-cant-specify-target-table-for-update-in-from-clause
我采取的是将id ,放到一个临时表中;
(2):https://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause
添加select:
UPDATE tbl SET col = (
SELECT ... FROM (SELECT.... FROM) AS x);
这里面讲述了,mysql 5.7以上添加select 还是不好用,
However, beware that from MySQL 5.7.6 and onward, the optimiser may optimise out the subquery, and still give you the error. Luckily, the optimizer_switch
variable can be used to switch off this behaviour; although I couldn't recommend doing this as anything more than a short term fix, or for small one-off tasks.
SET optimizer_switch = 'derived_merge=off';
Thanks to Peter V. Mørch for this advice in the comments.
Example technique was from Baron Schwartz, originally published at Nabble, paraphrased and extended here.
(3);执行带索引:
1\ update t set t.sex = 'm' where t.id in (select id from d); //explain 感觉很慢;
正确做法:
update t,d set t.sex = 'm'where t.id = d.id; //soso..
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· Linux glibc自带哈希表的用例及性能测试
· 深入理解 Mybatis 分库分表执行原理
· 如何打造一个高并发系统?
· .NET Core GC压缩(compact_phase)底层原理浅谈
· 现代计算机视觉入门之:什么是图片特征编码
· 手把手教你在本地部署DeepSeek R1,搭建web-ui ,建议收藏!
· Spring AI + Ollama 实现 deepseek-r1 的API服务和调用
· 数据库服务器 SQL Server 版本升级公告
· 程序员常用高效实用工具推荐,办公效率提升利器!
· C#/.NET/.NET Core技术前沿周刊 | 第 23 期(2025年1.20-1.26)