Altering value for multiple entries

Question

I have a query that pulls up all of the patients that I need to have the row called health_home marked as 'Y' instead of null, but only the 180 people in their query and only their maximum episode. Not sure how to do the alter well still keeping the maximum episode. My query and results are shown below. Thank you in advance and I am running sql server 2005.

select 

patient_id,
episode_id,
health_home

from (select p.*,
    max(episode_id) over (partition by patient_id) as maxei
from patient_custom p) p
where patient_id in ('00017186', '00000308', '00007226', '000147016', '00017016', '00001355', '00000666', '00019633', '00020048', '00008553',
 '00011520', '00004374', '00000485', '00000578', '00000040', '00008686', '00001983', '00001051', '00020168', '00012135', '00000591', 
'00000171', '00000998', '00003820', '00015625', '00001160', '00019386', '00020625', '00015930', '00007269', '00002913', '00003325',
'00013529', '00020454', '00001439', '00017131', '00003488', '00006453', '00004982', '00000888', '00020433', '00011334', '00016275',
'00013526', '00013103', '00017936', '00002654', '00019942', '00018198', '00008726', '00012458', '00020579', '00014071', '00004858',
'00013110', '00002799', '00000170', '00000617', '00018516', '00020896', '00002229', '00016442', '00000828', '00020204', '00013838',
'00019351', '000202763', '00010412', '00020429', '00020504', '00002041', '00016633', '00019463', '00007017', '00000307', '00005067', 
'00015469', '00013147', '00015511', '00000822', '00008882', '00001368', '00000036', '00000036', '00016745', '00010081', '00011721', 
'00003569', '00000599', '00018126', '00001579', '00015647', '00016056', '00002281', '00008362', '00000744', '00019227', '00018016',
'00003315', '00000461', '00020335', '00006962', '00018647', '00000864', '00009559', '00001997', '00014520', '00000965', '00000812',
'00003851', '00012100', '00015446', '00001392', '00014688', '00019997', '00016236', '00006070', '00019686', '00006070', '00019686',
'00019374', '00001192', '00007437', '00015716', '00014616', '00015905', '00009934', '00014047', '00016940', '00015307', '00016000', 
'00007639', '00016300', '00010460', '00010460', '00007891', '00006745', '00001892', '00018362', '00006817', '00001548', '00004646', 
'00016971', '00006634', '00006634', '00006631', '00011830', '00016457', '00007321', '00017669', '00006625', '00005542', '00000163',
'00007254', '00018958', '00001333', '00010767', '00005146', '00007247', '00000097', '00000835', '00000529', '00017241', '00014287', 
'00012424', '00011239', '00020159', '00018979', '00005998', '00013930', '00016862', '00000395', '00004164', '00019676', '00001811',
'00011620', '00008858', '00014625', '00014778', '00018664', '00010215', '00015453', '00013298', '00019664', '00002272', '00006585',
'00010311', '00002140') and episode_id = maxei

Answer 1

Well, the rows you want to update are those that would be assigned row number 1, if you assigned row numbers separately for each patient and by descending episode number:

;With Ordered as (
    select p.*,
         ROW_NUMBER() OVER (PARTITION BY patient_id
                            ORDER BY episode_id DESC) as rn
    from patient_custom p
    where p.patient_id in ('00017186', ... , '00002140')
)
update Ordered set health_home = 'Y'
where rn = 1