complex update

Last Post 11 Sep 2009 07:22 AM by timcronin. 0 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
timcronin
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.

update
pharm.dbo.cms_dcmedorderimport
set cms_dcmedorderimport.rxno =
h.rxno,
cms_dcmedorderimport.rono =
h.rono
from rx..hrxs h
where cms_dcmedorderimport.cms_order_number =
h.orderid
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
You are not authorized to post a reply.

Acceptable Use Policy