有一次由于不太熟悉业务,忘了加唯一索引,导致数据库里不少重复数据。为了删除重复数据,编写下面的SQL语句,但是执行完后却报了如标题的错误。原因是:MySQL不允许在进行子查询的同时删除原表数据。
- DELETE FROM table1
- where order_id in
- (select order_id from table1 group by order_id having count(order_id) > 1)
- and id not in
- (select min(id) from table1 group by order_id having count(order_id)>1);
其中的一个解决办法如下,就是在将查询结果作为一个结果集,再从这个结果集中取数据,然后保留最小id的数据。SQL如下(第2,3,4,9,10为新添加的)。
- delete from table1
- where id in (
- select id from (
- select id from table1
- where order_id in
- (select order_id from table1 group by order_id having count(*) > 1)
- and id not in
- (select min(id) from table1 group by order_id having count(*)>1)
- ) as t
- );
上面的sql给DBA看到后,立马就给喷了,说这样执行太低,于是修改成下面这样:
- delete a from table1 a ,
- (select min(id) as ms ,order_id from table1 group by order_id having count(*)>1) b
- where a.order_id=b.order_id and a.id>b.ms
相关推荐
最近在工作中遇到了一个mysql错误提示1093:You can’t specify target table for update in FROM clause,后来通过查找相关的资料解决了这个问题,现在将解决的方法分享给大家,有需要的朋友们可以参考借鉴,下面来...
主要介绍了mysql中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
- You can specify browser's title, back button title, background and text colors - Uses native UI elements (native scrolling performance) - Shows spinner animation while website is loading - Bundled ...
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 find complete help in 'help.hlp' file ---------------------------------------------------------------------- Radmin ---------------------------------------------------------------------- ...
If you use an AT&T 6300 computer, contact AT&T to upgrade your ROM BIOS to version 1.21 or later. The ROM BIOS version number is displayed when you start your computer. 2.14 LANtastic -------------- ...
在平常的项目中,经常会碰到这样的问题:我需要在一张标中同时更新和查询出来的...结果却报错,报错信息为:You can't specify target table 'tb_test' for update in FROM clause,不能在同一语句中update,select同
You can also try asking for help in the Delphi newsgroups. Since the Drag and Drop Component Suite is in widespread use, there's a good chance another user can help you. I recommend the following ...
-t suppress header in output, for testing --list-arches list the arches in the file, then exit --sdk-ios specify iOS SDK version (will look in /Developer/Platforms/iPhoneOS.platform/Developer/SDKs/...
Please specify the final destination directory for installed Postfix configuration files. config_directory: [] bin/postconf: error while loading shared libraries: libmysqlclient.so.16: cannot open ...
This class creates sql to import into tables in any mysql table - you specify the fieldname in the csv - and the mysql field in your db that this relates to. It supports database queries to get ...
Update from TC 1.5 - This allows for an easy update from version 1.5. After the files are copied, all the installed options from your current TC.EXE will be transferred to the new one. This is ...
(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 use commands from the Windows Command Prompt or a batch file to control Diskeeper. Group Policy Support -------------------- Diskeeper can be configured network-wide with the Group Policy ...