You can't specify target table 't_coupon_new ' for update in FROM clause
在给mysql做数据处理时,一开始写了如下sql来删除重复导入的数据:
delete from t_coupon_new where id in( select e.id
from t_coupon_new e,t_coupon_new f where e.sourceid=f.sourceid and
e.source=f.source and e.createtime<f.createtime)
但一执行,出现标题所示的异常。
百度一下,知道了原因:这个异常的 大概意思就是 对于更新中的from语句,你指定不了一个目标表 t_coupon_new 。 大概就是 随着你的删除操作 前面的重复条件查询结果集也在动态的跟新,这样如果还允许你删除的话,后果是无法想象的,所以这时不能让你删除数据。 数据库系统原理 上是这么说的Problem: as we delete tuples from deposit, the average balance changes Solution used in SQL: 1. First, compute avg balance and find all tuples to delete 2. Next, delete all tuples found above (without recomputing avg or
retesting the tuples)
如是,修改了一下SQL为:
delete from t_coupon_new where id in( select id from (select e.id id
from t_coupon_new e,t_coupon_new f where e.sourceid=f.sourceid and
e.source=f.source and e.createtime<f.createtime) a);
这样就好了。解决的原理就是:
就是让要查询出来的id集合不随删除 过程而动态的改变。 所以,1、先查询出所有重复的数据里,较早的那个数据的id作为一个结果集保存 2、再来根据这个结果集做删除操作。这样就好了。
不知道大家碰到是不是这样解决的,或者还有更好的解决的办法。一定要告诉我哦。
分享到:
相关推荐
主要介绍了mysql中You can’t specify target table for update in FROM clause错误解决方法,需要的朋友可以参考下
最近在工作中遇到了一个mysql错误提示1093:You can’t specify target table for update in FROM clause,后来通过查找相关的资料解决了这个问题,现在将解决的方法分享给大家,有需要的朋友们可以参考借鉴,下面来...
。。。
...
1093 – You can’t specify target table ‘t’ for update in FROM clause, Time: 0 前情提示: Mac OS10.14+MySQL8.0.18; centOS6.8final+MySQL5.6; 解决: 方法一:多嵌套一层。多来一层子查询 以上SQL修改...
mysql 一个较特殊的问题:You can't specify target table 'wms_cabinet_form' for update in F
mysql 语句如下: update wms_cabinet_form set cabf_enabled=0 where cabf_id in ( SELECT wms_cabinet_form.cabf_id FROM wms_cabinet_form Inner Join wms_cabinet ON wms_cabinet_form.cabf_cab_id = wms_...
在平常的项目中,经常会碰到这样的问题:我需要在一张标中同时更新和查询出来的...结果却报错,报错信息为:You can't specify target table 'tb_test' for update in FROM clause,不能在同一语句中update,select同
(b) the name of the table, the names of the table's attributes, the data types of the table's attributes, the formats of the table's attributes, and the maximum number of rows that the table can have...
You can specify for how many concurrent threads these steps will be executed and which Oracle accounts should be used. The performance results can be viewed in real-time as text, bar chart, or line ...
You can also specify the /logmsg switch to pass a predefined log message to the commit dialog. Or, if you don't want to pass the log message on the command line, use /logmsgfile:path, where ...
and which you don't want SQL Assistant to treat as keywords, for example, the default configuration includes ID and Name, names, you can add your own. "Show Keys and Indexed Columns" is preset for ...
If you don’t know the table owner or don’t want to enter it you can just enter a period. The idea of a custom autocomplete list is new. It gives you the ability to list anything you want. What ...
Download the 2013 v1 update to access the new TileControl to achieve a Windows 8 look and feel in your desktop apps as well as feature enhancements for Reports, Editor, and Scheduler. See the what's ...
Download the 2013 v1 update to access the new TileControl to achieve a Windows 8 look and feel in your desktop apps as well as feature enhancements for Reports, Editor, and Scheduler. See the what's ...
structure data from PDB files can now be used, and are stored in a database for lookup later dissect structures form can now show a list of known structures (pdb, mono, ...) Added a "revert to saved ...
o You can now specify wildcards (For example: nir*.doc, abc???.txt) in the include/exclude filename option. * Version 2.51 o Added option to choose the file size display unit: Bytes, KB, or MB. ...
If you currently work with VCLZip 2.X with TBlobStreams or some other type of streams, you can either define your own TkpBlobStream for instance which inherits from TkpHugeStream, or use the ...