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.
Advertisement
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