DELETE FROM users
WHERE NAME IN (SELECT NAME
FROM users
GROUP BY NAME
HAVING COUNT(NAME) > 1)
AND
id NOT IN (SELECT MIN(id)
FROM users
GROUP BY NAME
HAVING COUNT(NAME)>1)
這樣寫的話會提示
Error Code : 1093
You can't specify target table 'users' for update in FROM clause
改成>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
DELETE users AS a FROM users AS a,
(
SELECT *,MIN(id) FROM users GROUP BY name HAVING COUNT(1) > 1
) AS b
WHERE a.name = b.name AND a.id > b.id;