Home>

There is the following table called Table A. (Actual data is about 200,000)

No EmployeeName DepartmentNo
1 Suzuki I 1
2 Suzuki II 2
3 Suzuki III 3
Four Suzuki IV 1
Five Suzuki V 2
6 Suzuki VI 3
... etc

The final landing point is to leave one department number for each department and delete the duplicated data.

You can get the list of Department Nos with the following SQL. (About 300)

select DepartmentNo
 from TableA
 group by DepartmentNo
 having count (*)>1


With reference to this, I created the following SQL to delete the data other than the acquired data.

delete from TableA
 where (Department) not in (
         select DepartmentNo
         from TableA
         group by DepartmentNo
         having count (*)>1
        )

However, when the above SQL is executed, the number of data I want to keep is
It is less than when the list was acquired (about 30), and the target number cannot be DELETEd.

I don't understand why it doesn't work.
If anyone knows the cause, please let me know.

Site used as a reference site