mysql 删除重复记录 保留一条

本文探讨了MySQL中常见的1093错误——无法在FROM子句中指定目标表进行更新的问题,并提供了三种有效解决方案:调整SQL语句结构、使用临时表以及创建临时表查询。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >


-- 方法一
/**

错误码: 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




[url=https://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause]MySQL Error 1093 - Can't specify target table for update in FROM clause[/url]

[quote]
***************
*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.
[/quote]
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值