开发者

how to delete duplicates in mysql using case

开发者 https://www.devze.com 2022-12-23 21:31 出处:网络
Right now I am using something like this to delete duplicates in mysql table : delete t2 from my_table1 as t1, my_table1 as t2 where

Right now I am using something like this to delete duplicates in mysql table :

delete t2 from my_table1 as t1, my_table1 as t2 where
t1.TestCase = t2.TestCase and t2.id > t1.id;

say I have a structure like this :

ID  Tes开发者_运维技巧tCAse Result  
1   T1       PASS
2   T2       FAIL
3   T3       FAIL
4   T3       PASS

now, in the above case T3 is duplicate entry, and if I use the SQL that I mentioned above, it would delete 4th row where the result is PASS, but this is the row that I want to keep and I want row 3 to get deleted which is FAIL.

Any help please?

Thank you.


if I undersstand correctly in case of duplicate you want to delete the "FAIL" and not the "PASS" ? in this case you can have the following query:

delete t2 from my_table1 as t1, my_table1 as t2 where
t1.TestCase = t2.TestCase and t2.id != t1.id and t2.Result='FAIL'; 

but what do you want to do when all the duplicate have "FAIL" in their column result? With the query above, both will be removed. Do you want to keep one in this case ?

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号