mysql 删除重复数据，并保存最新一条数据

 

删除重复行

1 DELETE FROM ecm_member_login_session 
2 WHERE (number , client_code) IN (
3         SELECT number, client_code FROM mall.ecm_member_login_session GROUP BY number , client_code HAVING COUNT(*) > 1) 
4     AND update_time NOT IN (
5         SELECT MAX(update_time) FROM mall.ecm_member_login_session GROUP BY number , client_code HAVING COUNT(*) > 1);

 

但是报1093错误

1 14:57:04    DELETE FROM ecm_member_login_session  WHERE (number , client_code) IN (   SELECT number, client_code FROM mall.ecm_member_login_session GROUP BY number , client_code HAVING COUNT(*) > 1)   AND update_time NOT IN (   SELECT MAX(update_time) FROM mall.ecm_member_login_session GROUP BY number , client_code HAVING COUNT(*) > 1)    
2 
3 Error Code: 1093. You can't specify target table 'ecm_member_login_session' for update in FROM clause    0.046 sec

 

后来在 嵌套查询语句里面再嵌套一层即可， 是因为mysql限制update（包括delete）操作表名与嵌套子查询语句里的表名一样的原因。

 

语句改为如下

1 DELETE FROM ecm_member_login_session 
2 WHERE (number , client_code) IN (
3         SELECT number, client_code from (SELECT number, client_code FROM ecm_member_login_session GROUP BY number , client_code HAVING COUNT(*) > 1) A) 
4     AND update_time NOT IN (
5         SELECT update_time from (SELECT MAX(update_time) FROM ecm_member_login_session GROUP BY number , client_code HAVING COUNT(*) > 1) A);

 

 mysql文档：

 1 上面是目前MYSQL5.0仍然有的限制，文档中说：
 2 
 3 In general, you cannot modify a table and select from the same table in a subquery. For example, this limitation applies to statements of the following forms:
 4 
 5 DELETE FROM t WHERE ... (SELECT ... FROM t ...);UPDATE t ... WHERE col = (SELECT ... FROM t ...);{INSERT|REPLACE} INTO t (SELECT ... FROM t ...);
 6 
 7 Exception: The preceding prohibition does not apply if you are using a subquery for the modified table in the
 8 
 9 FROM
10 
11 clause. Example:
12 
13 UPDATE t ... WHERE col = (SELECT (SELECT ... FROM t...) AS _t ...);
14 
15 Here the prohibition does not apply because a subquery in the
16 
17 FROM
18 
19 clause is materialized as a temporary table, so the relevant rows in
20 
21 t
22 
23 have already been selected by the time the update to
24 
25 t
26 
27 takes place.

 

 

 

 

 

参考：

mysql error 1093 解决方法
SQL删除重复数据只保留一条

 http://blog.163.com/xiaoqiu_1120/blog/static/121632322007112411424982/

posted @ 2015-06-25 15:12  xunux  阅读(1997)  评论(0)  编辑  收藏  举报