0

I got the problem that on one table it sometimes makes a double entry. I want to delete the doubled entry. I only want to delete a row when both values are the same than on another row. How is this possible?

My DB structure:

enter image description here

Example for the double entries:

enter image description here

Taryn
  • 242,637
  • 56
  • 362
  • 405
NiSu
  • 105
  • 1
  • 16

1 Answers1

1

One way to do it is using exists:

DELETE t0
FROM Password_Department t0
WHERE EXISTS
(
    SELECT 1
    FROM Password_Department t1
    WHERE t0.PasswordFK = t1.PasswordFK
    AND t0.DepartmentFK = t1.DepartmentFK
    AND t0.Id > t1.Id
)

If you prefer the row number method - 

delete x 
from (
   select *, 
          rn = row_number() over (partition by DepartmentFK, PasswordFK order by Id)
   from Password_Department 
) x
where rn > 1

After you deleted the duplicate entries, you should add a unique constraint on PasswordFK and DepartmentFK:

ALTER TABLE Password_Department 
ADD Constraint UC_Password_Department UNIQUE (PasswordFK , DepartmentFK)
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121