CODEWITHSUNDEEP
sqlduplicates
Robby Johnston
Robby Johnston

Reputation: 191

Updating table column where one column is max value

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.

Upvotes: 3

Views: 30203

Answers (3)

Zane Bien
Zane Bien

Reputation: 23125

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.

Upvotes: 4

Kevin Aenmey
Kevin Aenmey

Reputation: 13429

You could also use the RANK() function.

UPDATE a
SET a.Message =  'duplicate encounter'
FROM MyTable a
    INNER JOIN (
        SELECT Encounter
            ,MRN
            ,RANK() OVER(PARTITION BY Encounter ORDER BY Admitted DESC) AS RankVal
        FROM MyTable) b
    ON a.Encounter = b.Encounter
    AND a.MRN = b.MRN
    AND b.RankVal <> 1

Upvotes: 0

Mark Byers
Mark Byers

Reputation: 838826

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

Upvotes: 5

Related Questions