从mysql表中删除重复的旧记录

j5fpnvbx 于 2021-06-19 发布在 Mysql

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

我有下面的查询，这是预期的工作。并返回需要删除的行。

SELECT * FROM tests WHERE e2e_product_id=407 AND test_name='PWBAR-FullBalance-Auth-TC1' AND
 test_id<>(SELECT MAX(test_id)AS testid FROM tests WHERE test_name IN 
 (SELECT test_name FROM tests WHERE e2e_product_id=407 GROUP BY test_name HAVING COUNT(*) >1) AND e2e_product_id=407 GROUP BY test_name)

我刚刚将上面的查询更改为delete语法，但它返回异常

Delete FROM tests WHERE e2e_product_id=407 AND test_name='PWBAR-FullBalance-Auth-TC1' AND
 test_id<>(SELECT MAX(test_id)AS testid FROM tests WHERE test_name IN 
 (SELECT test_name FROM tests WHERE e2e_product_id=407 GROUP BY test_name HAVING COUNT(*) >1) AND e2e_product_id=407 GROUP BY test_name)

错误：
错误代码：1093不能在from子句中指定更新的目标表“tests”
如何使用上述方法删除数据。有线索吗？

mysql

来源：https://stackoverflow.com/questions/52433016/delete-the-duplicate-old-records-from-mysql-table

2条答案

按热度按时间

whlutmcx1#

把你的钥匙换成你的钥匙

DELETE FROM TESTS WHERE YOUR_KEY IN(

    SELECT YOUR_KEY FROM tests WHERE e2e_product_id=407 AND test_name='PWBAR-FullBalance-Auth-TC1' AND
     test_id<>(SELECT MAX(test_id)AS testid FROM tests WHERE test_name IN 
     (SELECT test_name FROM tests WHERE e2e_product_id=407 GROUP BY test_name HAVING COUNT(*) >1) AND e2e_product_id=407 GROUP BY test_name)

)

赞(0）回复(0）举报 2021-06-20

olmpazwi2#

我想你可以试试这个-

Delete FROM tests
 WHERE e2e_product_id = 407
   AND test_name = 'PWBAR-FullBalance-Auth-TC1'
   AND test_id <> (SELECT MAX_TEST_ID
                     FROM (SELECT test_name, MAX(test_id) MAX_TEST_ID
                             FROM tests
                            WHERE e2e_product_id = 407
                            GROUP BY test_name
                           HAVING COUNT(*) > 1) TEMP)

赞(0）回复(0）举报 2021-06-20

我来回答

从mysql表中删除重复的旧记录

2条答案

相关问题

热门标签

最新问答