(1093，u“不能在from子句中指定更新的目标表'wallet\u consume\u record')

guicsvcw 于 2021-08-09 发布在 Java

关注(0)|答案(2)|浏览(350)

我想从MySQL5.7中删除Duplicate数据，这是我的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';

为什么以及如何让它工作？我还试着 Package ：

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

sql mysql Join select sql-delete

来源：https://stackoverflow.com/questions/62280864/1093-uyou-cant-specify-target-table-wallet-consume-record-for-update-in-fr

2条答案

按热度按时间

wdebmtf21#

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

赞(0）回复(0）举报 2021-08-09

zynd9foi2#

您可以使用组\u concat。
创建两个用户定义的函数，返回记录的组concat值，并在查询中使用。
i、 e（其中一个函数看起来像）

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());

对于非in值，可以使用相同的值

赞(0）回复(0）举报 2021-08-09

我来回答

(1093，u“不能在from子句中指定更新的目标表'wallet\u consume\u record')

2条答案

相关问题

热门标签

最新问答