How to delete duplicate rows in a table based on what is supposed to be a unique column referring to a table in another database

Table A

LoginID|FacilityID|LoginName|PersonID|DateOfBirth|
    111|        20| Person_A|     101| 01/01/1990|
    212|        20| Person_B|     230| 02/02/1991|
    456|        20| Person_C|     101| 03/03/1992|
    987|        20| Person_A|     808| 01/01/1990|

Table B

PersonID|FacilityID|LastName|FirstName|DateOfBirth|
     101|        20|   Stone|     Beth| 03/03/1992|
     230|        20|   Jones|      Bob| 02/02/1991|
     808|        20|   Brown|     Jack| 01/01/1992|

In Table A PersonID refers to Table B another database and is supposed to be unique here. For reasons, it isn’t and a stored procedure that uses PersonID and FacilityID as input parameters is returning multiple rows for the instances of that duplicate PersonID. I would like to remove the top record in Table A as it does not belong by comparing it to what the PersonID is supposed to be.

I thought about comparing the PersonID and DOB here against Table B and remove records that didn’t match, however, I haven’t been able to come up with the way to do it.

Answer

I understand that you want to delete records for which no corresponding record exists in the other table with the same PersonId and DateOfBirth.

If so, you can use an anti-left join as follows:

delete t
from mytable t
left join myothertable t1 
    on t1.PersonId = t.PersonId and t1.DateOfBirth = t.DateOfBirth
where t1.personId is null