#1093 - You can't specify target table 'user' for update in FROM clause
来源:互联网 发布:我知谁掌管明天视频 编辑:程序博客网 时间:2024/05/16 14:59
报出“1093 - You can't specify target table 't' for update in FROM clause”的异常。意思,意思大约为,你不能指定更新的目标表在FROM子句中(英文不好,即使认识各个单词,串起来就不行了。。。)
就如文档所述“Currently, you cannot update a table and select from the same table in a subquery.”,见http://dev.mysql.com/doc/refman/5.5/en/update.html。
不知道MySQL为什么不允许这样操作,猜,可能是担心更新的表与查询的表为同一表会存在嵌套递归?还是担心效率的问题呢?
原代码:
DELETE from `user` where id not in (select MAX(id) from `user` group by telephone HAVING COUNT(telephone)>=1 ORDER BY NULL)
如果,将该表在嵌套一层,即“(select * from t_area) st”这样得出一个临时的结果集,即无报错,但,这性能较差,仅仅适合较小的数据量的。(见此讨论帖:http://stackoverflow.com/questions/17742214/you-cant-specify-target-table-name-for-update-in-from-clause)。
修改后如下:
DELETE FROM `user` WHERE id NOT IN (SELECT mid from (select MAX(id) as mid from `user` group by telephone HAVING COUNT(telephone)>=1 ORDER BY NULL) as temtable);
分析:先把要删除的目标放到一张临时表再把要删除的条件指定到这张临时表即可。
0 0
- #1093 - You can't specify target table 'user' for update in FROM clause
- mysql update语句问题:You can't specify target table 'user' for update in FROM clause
- 【MySQL】1093 You can't specify target table 'table name' for update in FROM clause
- [Err] 1093 - You can't specify target table 'table' for update in FROM clause处理
- [Err] 1093 - You can't specify target table 's' for update in FROM clause
- MYSQL 临时表 #1093 - You can't specify target table 'dealer_admin' for update in FROM clause
- ERROR 1093 (HY000): You can't specify target table 'b' for update in FROM clause
- [Django](1093, "You can't specify target table 'fee_details_invoices' for update in FROM clause") 错误
- [Err] 1093 - You can't specify target table 'image' for update in FROM clause
- MYSQL 1093 - You can't specify target table '' for update in FROM clause
- ERROR 1093 (HY000): You can't specify target table 'SDS' for update in FROM clause
- MySQL 错误码: 1093 You can't specify target table 'jc_user' for update in FROM clause
- mysql错误:1093-You can’t specify target table for update in FROM clause的解决方法
- You can't specify target table 'table name' for update in FROM clause
- MySql报错: You can't specify target table 'table name' for update in FROM clause
- You can't specify target table 't' for update in FROM clause
- Mysql - You can't specify target table 'dataproduct' for update in FROM clause
- You can't specify target table 'wms_cabinet_form' for update in FROM clause
- STM32芯片ADC内部的CH17参考电压的用途
- MySQL SQL语句优化技巧
- 懒惰的自己,自带标题栏的BaseActivity
- 自动化测试-appium框架环境配置
- Android 生成系统签名的KeyStore
- #1093 - You can't specify target table 'user' for update in FROM clause
- spring mvc需要的 配置文件(以及相关类和接口的详细解释):
- Java 动态类加载与重载
- osg PhysX的例子
- OpenCV 3.x Lib 源码结构简介
- C#第15章
- 安装和配置Postfix
- centos定时任务进行数据库数据备份
- linux内核参数调整