我需要从
mysql中删除表中的重复记录.
所以我有一个表名“employee”字段是empid,empname,empssn
为了获得重复记录我写了一个查询
SELECT COUNT(empssn), empssn
FROM employee
GROUP BY empssn
HAVING COUNT(empssn)>1
现在我想删除重复的记录.因为我有书面查询是.
DELETE FROM employee
WHERE (empid, empssn) NOT IN (SELECT MIN(empid), empssn
FROM employee
GROUP BY empssn);
你可以假设表中的记录是
EmpId EmpName EmpSSN
-------------------------------
1 Jack 555-55-5555
2 Joe 555-56-5555
3 Fred 555-57-5555
4 Mike 555-58-5555
5 Cathy 555-59-5555
6 Lisa 555-70-5555
7 Jack 555-55-5555
8 Mike 555-58-5555
9 Cathy 555-59-5555
10 Lisa 555-70-5555
11 Lisa 555-70-5555
但我有一个mysql错误是
You can’t specify target table ’employee’ for update in FROM clause