complex update

Last Post 11 Sep 2009 07:22 AM by timcronin. 0 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

11 Sep 2009 07:22 AM
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 =
cms_dcmedorderimport.rono =
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

Acceptable Use Policy