Help me do this SQL update query correctly.

Question

I have 3 values that I need to copy from one table to another table. Here is my amateur attempt at it, I know it is horribly ineffecent, what would the correct way of doing this query?

  update [IDAT_PATIENTS] 
  set TargetRabiesSerial = 
    (select top 1 SERIAL_NUMBER 
     from [IDAT_RABIESHISTORY] as rab 
     where TargetPetAccountNum = rab.PATIENT_ACCOUNT_ID 
        and TargetClientAccountNum = rab.CLIENT_ACCOUNT_ID 
     order by rab.DATE_TIME_PERFORMED desc)
  , TargetRabiesBrandName = 
    (select top 1 BRAND_NAME 
     from [IDAT_RABIESHISTORY] as rab 
     where TargetPetAccountNum = rab.PATIENT_ACCOUNT_ID 
        and TargetClientAccountNum = rab.CLIENT_ACCOUNT_ID 
     order by rab.DATE_TIME_PERFORMED desc)
  , TargetRabiesTag = 
    (select top 1 TAG_NUMBER 
     from [IDAT_RABIESHISTORY] as rab 
     where TargetPetAccountNum = rab.PATIENT_ACCOUNT_ID 
        and TargetClientAccountNum = rab.CLIENT_ACCOUNT_ID 
     order by rab.DATE_TIME_PERFORMED desc)
  where TargetClientAccountNum in 
    (select CLIENT_ACCOUNT_ID 
     from [IDAT_RABIESHISTORY] 
     where TargetPetAccountNum = PATIENT_ACCOUNT_ID)

Answer 1

Try This:

  Update p Set
     TargetRabiesSerial = h.SERIAL_NUMBER,
     TargetRabiesBrandName =  h.BRAND_NAME,  
     TargetRabiesTag = h.TAG_NUMBER   
  From IDAT_PATIENTS p
      Join IDAT_RABIESHISTORY h
          On h.PATIENT_ACCOUNT_ID = p.TargetPetAccountNum
             And h.CLIENT_ACCOUNT_ID = p.TargetClientAccountNum 
             And h.DATE_TIME_PERFORMED = 
                    (Select Max(DATE_TIME_PERFORMED)
                     From IDAT_RABIESHISTORY 
                     Where h.CLIENT_ACCOUNT_ID = p.TargetClientAccountNum 
                         And h.CLIENT_ACCOUNT_ID = p.TargetClientAccountNum)
  Where TargetClientAccountNum In  
    (Select CLIENT_ACCOUNT_ID  
     From IDAT_RABIESHISTORY  
     Where TargetPetAccountNum = PATIENT_ACCOUNT_ID) 

Answer 2

Something more or less like this, didn't have a schema to validate against so double check the syntax :)

update [IDAT_PATIENTS] 
    set TargetRabiesSerial = tblLatestHistory.SERIAL_NUMBER,
    TargetRabiesBrandName = IDAT_RABIESHISTORY,
    TargetRabiesTag = IDAT_RABIESHISTORY
from [IDAT_PATIENTS]
inner join (
    select PATIENT_ACCOUNT_ID, CLIENT_ACCOUNT_ID, SERIAL_NUMBER, BRAND_NAME, TAG_NUMBER, max(DATE_TIME_PERFORMED) 
    from IDAT_RABIESHISTORY
    group by  PATIENT_ACCOUNT_ID, CLIENT_ACCOUNT_ID, SERIAL_NUMBER, BRAND_NAME, TAG_NUMBER
) tblLatestHistory
on TargetPetAccountNum = tblLatestHistory.PATIENT_ACCOUNT_ID 
and TargetClientAccountNum = tblLatestHistory.CLIENT_ACCOUNT_ID