0

It is most likely that my syntax is off and I just don't realize it.

I have a table that is defined this way:

-- Get all of the PatientVisitOID for which we want data
DECLARE @VisitOID TABLE (PatientVisitOID INT)

INSERT INTO @VisitOID
SELECT DISTINCT PatientVisitOID
FROM SMSDSS.c_covid_ptvisitoid_tbl

I then go about getting data into tables @PatientVisitDataDSS (Historical Data Warehouse) and @PatientVisitDataPRD (Production for today only on any given day)

I run a query like this which works (Delete all records from the temp datawhare house table where the visit id is in PROD - we want the prod version):

DELETE FROM @PatientVisitDataDSS
WHERE PatientVisitOID IN (SELECT A.PatientVisitOID FROM @PatientVisitDataPRD AS A)

And then directly below it I run this (which does not work - Delete records from the prod temp table that are not in our original list of id numbers in @VisitOID) I feel this should drop 422 records and keep 76:

DELETE FROM @PatientVisitDataPRD
WHERE PatientVisitOID NOT IN (SELECT A.PatientVisitOID FROM @VisitOID AS A)

I know records exist that should be deleted because I can run the following and get a couple hundred records (422 Records):

SELECT B.PatientVisitOID
FROM @VisitOID AS A
RIGHT JOIN @PatientVisitDataPRD AS B ON A.PatientVisitOID = B.PatientVisitOID
WHERE A.PatientVisitOID IS NULL

I can also see the records that should be kept by running (76 Records):

SELECT A.PatientVisitOID
FROM @PatientVisitDataPRD AS A
INNER JOIN @VisitOID AS B ON A.PatientVisitOID = B.PatientVisitOID

There are currently as of writing 498 records in @PatientVisitDataPRD with 76 records in the INNER JOIN and 422 records in the right join where A.PatientVisitOID IS NULL which leads me to believe that these 422 records should get dropped from the DELETE FROM @PatientVisitDataPRD query.

MCP_infiltrator
  • 3,961
  • 10
  • 45
  • 82
  • 1
    Does this answer your question? [NOT IN vs NOT EXISTS](https://stackoverflow.com/questions/173041/not-in-vs-not-exists) – Charlieface Mar 03 '21 at 16:25

1 Answers1

2

NOT IN with a subquery is highly not recommended because it behaves unexpectedly if any value in the subquery is NULL. In your case, this would result in no rows being deleted, which I think is your problem.

Instead, try NOT EXISTS:

DELETE pv
    FROM @PatientVisitDataPRD pv
    WHERE NOT EXISTS (SELECT 1
                      FROM @VisitOID v
                      WHERE v.PatientVisitOID = pv.PatientVisitOID
                     );
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786