我想从MySQL5.7中删除dulplicate数据，这是我的SQL:
DELETE FROM wallet_consume_record
WHERE trans_no IN (
SELECT trans_no
FROM wallet_consume_record
GROUP BY trans_no
HAVING count(trans_no) > 1)
AND id NOT IN (
SELECT min(id)
FROM wallet_consume_record
GROUP BY trans_no
HAVING count(trans_no) > 1
);
但当我执行它时，它抛出:
(1093, u"You can't specify target table 'wallet_consume_record' for update in FROM clause")
我已经试过:
SET optimizer_switch = 'derived_merge=off';
为什么和如何使它发挥作用？ 我也试着包装:
DELETE FROM wallet_consume_record
WHERE trans_no IN (
SELECT trans_no
FROM wallet_consume_record
GROUP BY trans_no
HAVING count(trans_no) > 1)
AND id NOT IN (
select c.id from
(
SELECT min(id) as id
FROM wallet_consume_record
GROUP BY trans_no
HAVING count(trans_no) > 1
) as c
);
还是不起作用。
mysql root@10.244.5.47:meow_report_pro> select version();
+-------------+
| version() |
|-------------|
| 5.7.29-log |
+-------------+
1 row in set
Time: 0.001s
MySQL不接受在同一查询的from子句中引用正在更新或删除的表。
您可以使用join来表示:
delete w
from wallet_consume_record w
inner join (
select trans_no, min(id) min_id
from wallet_consume_record
group by trans_no
having count(*) > 1
) w1 on w.trans_no = w1.trans_no and w.id > w1.min_id
可以使用GROUP_CONCAT。
制作2个用户定义函数，返回记录的组连接值，并在查询中使用它。
即（其中一个函数如下所示）
CREATE FUNCTION `fn_get_tran_no_in_records`()
RETURNS varchar(MAX) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
BEGIN
declare var_records varchar(10) default '';
SELECT group_concat(trans_no)
FROM wallet_consume_record into var_records
GROUP BY trans_no
HAVING count(trans_no) > 1
return var_records;
END
您可以简单地在查询中使用它，如下所示
DELETE FROM wallet_consume_record
WHERE trans_no IN (fn_get_tran_no_in_records());
在值中可以使用相同的NOT
