I'm trying to update my lab_nor table based on the status of both the lab_nor and order tables. I want to change the status and cancel description in the lab_nor table if the ngn_lab_status = 'Opened' and then order_.actStatus field = 'ordered'. However, all records with lab_nor.ngn_lab_status = 'Ordered' are being updated whether or not order.actStatus = 'ordered'. Here is my SQL:
update lab_nor
set cancel_reason = 'due to inactivity', ngn_status = 'Cancelled'
from lab_nor ln
inner join order_ o on ln.enc_id = o.encounterID
where o.actStatus = 'ordered'
and ln.ngn_status = 'Ordered'
and o.encounterDate < '20160301'
and o.actClass = 'LAB'
What am I doing wrong??
update lab_nor
set cancel_reason = 'due to inactivity', ngn_status = 'Cancelled'
from lab_nor ln
inner join order_ o on ln.enc_id = o.encounterID
where o.actStatus = 'ordered'
and ln.ngn_status = 'Ordered'
and o.encounterDate < '20160301'
and o.actClass = 'LAB'
What am I doing wrong??