bug_x

导航

< 2025年1月 >
29 30 31 1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 31 1
2 3 4 5 6 7 8
 

执行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..

posted on   bug_x  阅读(1142)  评论(0编辑  收藏  举报
编辑推荐:
· 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)
 
点击右上角即可分享
微信分享提示