Updating table column where one column is max value

duplicates, sql

Solution

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.

Problem

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.

Original source