maxid value with a join for update

[timc]

I have history table a which looks like below

DRUGID CATID
A333
A333
A334

I have a cateogory table which looks like below

DRUGID CATID GROUPID
A333 AME 99
A333 AMR 20

I need to update the CATID field in history table
with the CATID from the category table which has the max (largest groupid). SO the CATID for DRUGID A333 should be
AME, because the GROUPID is larger). Trying to avoid cursor to do this. Is a subquery possible? THe CATID is the linking field

[Stephen]

This should be pretty close :

Update History
Set CatID = C.CatID
From History H
Inner Join Cateogory C
On C.DrugID = H.DrugID
Where C.GroupID = (Select Max(C2.GroupID)
From Cateogory C2
Where C2.DrugID = C.DrugID)