mysql语句查找重复记录大于1的,在MySQL中删除计数大于1的重复行

Need some help on a MySQL query to delete the rows where the userID count is greater than one and has an acl value=4.

What I've tried as the select query:

DELETE FROM `users_acl`

WHERE userID IN(

SELECT * FROM `users_acl`

WHERE `acl` BETWEEN 2 AND 4

GROUP BY userID

HAVING COUNT(userID) > 1

);

But can't find a proper way to delete the rows in the same query.

Table structure

+--------+-----+---------+

| userID | acl | deleted |

+--------+-----+---------+

| 27 | 2 | 0 |

| 28 | 2 | 0 |

| 31 | 2 | 0 |

| 42 | 2 | 0 |

| 42 | 4 | 0 |

| 45 | 1 | 0 |

| 51 | 1 | 0 |

| 54 | 1 | 0 |

| 63 | 2 | 0 |

| 63 | 4 | 0 |

| 64 | 1 | 0 |

| 69 | 2 | 0 |

| 73 | 2 | 0 |

| 73 | 4 | 0 |

| 76 | 1 | 0 |

| 77 | 2 | 0 |

| 77 | 4 | 0 |

+--------+-----+---------+

解决方案

In MySQL you can't select from a table you are deleting from at the same time. But with a temp table you can overcome this problem

DELETE FROM `users_acl`

WHERE userID IN

(

SELECT * FROM

(

SELECT userID

FROM `users_acl`

GROUP BY userID

HAVING COUNT(userID) > 1

AND SUM(`acl` = 4) > 0

) tmp

);

or use a joininstead

DELETE u

FROM `users_acl` u

JOIN

(

SELECT userID

FROM `users_acl`

GROUP BY userID

HAVING COUNT(userID) > 1

AND SUM(`acl` = 4) > 0

) tmp on tmp.userID = u.userID