1

The problem: I am trying to add some logic to a database refresh job that will de-identify PHI-related fields, and have come across a strange side effect. I am attempting to exclude a group of patients, in order to allow that set to see their true information, and mask all of the other sets. When I run my update statement, it also updates a large portion of that set I was trying to exclude. Why is that, and how can I change my update statement to avoid that behavior?

The data model:

  1. Patients table - ID as the key, and GroupID to differentiate groups of patients. Also has an IsDeleted field (bit) to indicate soft deletion.t item
  2. Phone table - ID as the key, and has three business fields: AreaCode, PhoneNumber, and Extension. Also has an IsDeleted field (bit) to indicate soft deletion.
  3. PatientPhone - Has an ID as the key, but also uses the combination (PatientID, PhoneID) to link these tables.

The relationship: Patient to PatientPhone - 1 to Many Phone to PatientPhone - 1 to Many

In order to validate before and after, I have been using this query to sample the phone numbers by grouping the first couple of digits. It gives me a wide distribution of values, as I would expect:

SELECT
P.GroupID, left(PH.AreaCode, 2) as ShortAreaCode, left(PH.PhoneNumber, 2) as ShortPhoneNumber,
    left(PH.Extension,2) as ShortExtension, count(*)
FROM dbo.Patient P
JOIN dbo.PatientPhone PP
ON P.ID = PP.PatientID
JOIN dbo.Phone PH
ON PP.PatientID = PH.ID
WHERE P.GroupID = 12345
GROUP BY P.GroupID, left(PH.AreaCode, 2), left(PH.PhoneNumber, 2), left(PH.Extension,2)
ORDER BY count(*) desc, P.GroupID, ShortAreaCode, ShortPhoneNumber, ShortExtension

I then run this update statement against the Phone table to default every other patient's phone number to 555-5555:

   UPDATE
        PH
        SET AreaCode = '555', PhoneNumber = '5555555', Extension = NULL
        FROM dbo.Phone as PH
        INNER JOIN dbo.PatientPhone as PP
        on PH.ID = PP.PhoneID
        INNER JOIN dbo.Patient as P
        on P.ID = PP.PatientID
        WHERE P.GroupID <> 12345 --This is the GroupID where we want to retain phone numbers

However, when I re-run my validation query, I see a couple of problems:

  1. Thousands of records I did NOT want to update were updated.
  2. Many of the other groups of Patients were NOT updated.

(Sample of the output afterwards): Grouping by count, of two patient groups In theory, the data model allows for phones to be related to more than one patient. We did have some issues in the past, and most of the phone numbers have been denormalized. I need help to understand:

  • How I can explain this side effect of the update statement
  • How I can adjust my update logic to exclude any 'shared' phone numbers, so that the set of patients not in one particular GroupID will have their phones defaulted

I would tend to believe the data (phones being shared) is likely the cause of this problem, but I am having trouble proving that. Can someone help me with this? Thanks for your time.

3

In the select, you're using a different table - dbo.phone_Copy. The update is running on dbo.phone.

  • It's a good observation, but I was trying to keep things simple and forgot to remove that one reference to dbo.phone_Copy. I am using a test copy of the database, and am actually working from a copy of the phone table. I insert all records into phone_Copy, and use it for the validations and update statements. In between each attempt, I drop the table and recreate it. Sorry if it caused confusion. I'll see if I can edit my original post so no one else sees that as the issue. – GregDBA Jan 16 '18 at 15:29
  • ahh, ok. One thing to try, what happens if you run your update as a select statement? What if you specify inner join in your first script? – James Jan 16 '18 at 19:49
  • Thanks for taking time on this. I'm going to post the answer now, since I see my mistake. – GregDBA Jan 17 '18 at 3:00
3

Thanks to the suggestions from others, and I appreciate the time anyone took reading this post. So after looking at this different ways, and taking several breaks, I now see that my validation query was at fault. The join was incorrect. The Update statement was designed correctly, I verified the data was valid in the tables, the relationships correct, and I was able to verify at a detailed level the update worked. Something about my validation query (that did aggregations) seemed off. My validation was incorrectly joining against the wrong foreign key.

The join:

JOIN dbo.Phone PH ON PP.PatientID = PH.ID

should have been stated as:

JOIN dbo.Phone PH ON PP.PhoneID = PH.ID

  • A good sample why using an ID column in each and every table is sometimes not really helpful. – Magier Jan 23 '18 at 12:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.