Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Update with Join Problem

Status
Not open for further replies.

mwake

Programmer
Feb 12, 2004
151
US
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??
 
Do you have any triggers on your tables? If you're not sure, run the following query.

Code:
sp_helptrigger 'lab_nor'


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
I do have 2 triggers on the lab_nor table. The isupdate column and isafter column of your query return 1, the rest of the columns 0
 
Since you have triggers on the table, it's possible that your triggers are somehow modifying your data such that the update statement is correct, but the triggers are changing the data to something you don't expect. I encourage you to check the code in both triggers to see if it could explain why your data is not behaving properly.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top