mysql保留一条重复,mysql 剔除重复记录 保留一条

mysql 删除重复记录 保留一条

-- 方法一

/**

错误码: 1093

You can't specify target table 'an' for update in FROM clause

用在select里的表,不能同时被修改

**/

DELETE an FROM t_anchor_cp an

WHERE an.c_order IN

(

SELECT t0.c_order FROM t_anchor_cp t0,(

SELECT t1.c_foreign_id,t1.c_line_type_id,t1.c_anchor_no FROM t_anchor_cp t1 GROUP BY

t1.c_foreign_id,t1.c_line_type_id,t1.c_anchor_no HAVING COUNT(*)>1

)t2

WHERE t0.c_anchor_no = t2.c_anchor_no

AND t0.c_foreign_id = t2.c_foreign_id

AND t0.c_line_type_id = t2.c_line_type_id

AND t0.c_order NOT IN

(

SELECT MIN(c_order) FROM t_anchor_cp t1 GROUP BY

t1.c_foreign_id,t1.c_line_type_id,t1.c_anchor_no HAVING COUNT(*)>1

)

)

-- 方法2 成功执行 使用临时表

DELETE an FROM t_anchor_cp an,(

SELECT t0.c_order FROM t_anchor t0,(

SELECT t1.c_foreign_id,t1.c_line_type_id,t1.c_anchor_no FROM t_anchor_cp t1 GROUP BY

t1.c_foreign_id,t1.c_line_type_id,t1.c_anchor_no HAVING COUNT(*)>1

)t2

WHERE t0.c_anchor_no = t2.c_anchor_no

AND t0.c_foreign_id = t2.c_foreign_id

AND t0.c_line_type_id = t2.c_line_type_id

AND t0.c_order NOT IN

(

SELECT MIN(c_order) FROM t_anchor_cp t1 GROUP BY

t1.c_foreign_id,t1.c_line_type_id,t1.c_anchor_no HAVING COUNT(*)>1

)

)tt

WHERE tt.c_order = an.c_order

-- 方法三 创建临时表,用于查询,数据量少的话

CREATE TABLE t_anchor_cp SELECT * FROM t_anchor

MySQL Error 1093 - Can't specify target table for update in FROM clause

引用

***************

*Cheekysoft:***

***************

In MySQL, you can't modify the same table which you use in the SELECT part.

Alternatively, try nesting the subquery deeper into a from clause ...

If you absolutely need the subquery, there's a workaround, but it's ugly for several reasons, including performance:

UPDATE tbl SET col = (

SELECT ... FROM (SELECT.... FROM) AS x);

The nested subquery in the FROM clause creates an implicit temporary table, so it doesn't count as the same table you're updating.