Updating table column where one column is max value

Question

I am trying to update one column in a series of about 113000 rows in a table. I have a table where there are a MRN, encounter, and admit columns in question. I have come across an issue where an encounter got entered into this table multiple times, but with different MRNS. I want to update a column called 'Message' with a value 'duplicate encounter' but ONLY if that encounter is not the most current admitted date.

For instance

Encounter               MRN                     Admitted
000000000497            0000097778              2006-01-04 20:26:00
000000000497            0000092892              2006-03-17 09:51:00
000000000497            0000003023              2008-08-15 09:50:00
000000000719            0000103691              2008-10-16 14:45:00
000000000719            0000048708              2006-05-26 08:04:00
000000000719            0000024123              2006-01-09 15:43:00
000000003390            0000099595              2006-03-13 11:30:00
000000003390            0000079713              2010-04-22 14:40:00

For the above info, I would want to update the message column for line 1 and 2 because line 3 is the most current admitted for encounter 000000000497. Lines 5 and 6 for 000000000719 because line 4 is its most current admit date.

I have tried searching through here but couldn't come up with anything. I just didn't know if I needed to create a cursor for this or what. Any help would be VERY much appreciated.

Solution 1

You can do:

UPDATE a
SET a.Message = 'Duplicate Encounter'
FROM tbl a
INNER JOIN
(
    SELECT encounter, MAX(admitted) AS maxadmitted
    FROM tbl
    GROUP BY encounter
    HAVING MAX(MRN) <> MIN(MRN)
) b ON a.encounter = b.encounter AND a.admitted <> b.maxadmitted

The subselect gets only the encounters that have more than one distinct MRN associated with it, and it also gets the max admitted date for each of those encounters.

We then only update rows where it matches the encounters returned from the subselect, but doesn't update the row with the most recent admitted date.

Solution 2

You can use a subselect to find the most recent Admitted for each Encouter:

UPDATE T1
SET Message = 'Duplicate'
FROM his..hpf_enc AS T1
WHERE Admitted <>
(
    SELECT MAX(Admitted)
    FROM his..hpf_enc AS T2
    WHERE T1.Encounter = T2.Encounter
)

You could also use a join:

UPDATE T1
SET Message = 'Duplicate'
FROM his..hpf_enc T1
JOIN
(
    SELECT Encounter, MAX(Admitted) AS Admitted
    FROM his..hpf_enc
    GROUP BY Encounter
) AS T2
ON T1.Encounter = T2.Encounter
WHERE T1.Admitted <> T2.Admitted

Comments

[Zane Bien]

Your solutions will update all encounters (minus the ones with max admitted dates) regardless of whether or not they have multiple distinct MRNs. I believe the OP only wants to update encounters which have more than one distinct MRN associated with it.

[Mark Byers]

He says in the question that the MRNs are different.