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

Question

我有以下查询，它按预期工作。并返回我需要删除的行。

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

错误:

Error Code: 1093 You can't specify target table 'tests' for update in FROM clause

如何使用上述方法删除数据。有线索吗？

Answer 1

我想你可以尝试这个 -

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)