I have a table to that is updated from a history table. The update happens because on a join of tables linked by patient id and order number shown below. In a perfect world this works.
set cms_dcmedorderimport.rxno =
from rx..hrxs h
where cms_dcmedorderimport.cms_order_number =
and cms_dcmedorderimport.patientid = h.patid
Now the complex part, and orderid and a patient id are only unique within a group of locations. Both of the tables ablove have a 3rd field which is a facility id which joins to a facility table. And since some of these patients can move around within a location there is no 1 to 1 match for the facility id, just that the facility is part of that contract.
On the facility table there is a contractid if which several facilities will belong to. So I need my update where clause to also include to make sure that the orderid and patientid for the first table (cms_dcmedorderimport) link to the hrxs table and that for both tables the contractid would be the same