CODEWITHSUNDEEP
sql-server
Dean.DePue
Dean.DePue

Reputation: 1013

Update column in table based on another table field

I have a prescription table that I want to set non active based upon a condition in the patient table. Will this work?

 update Prescription
set active = 0
from prescription left join patients on patients.id = prescription.patientid
where patients.[site] = @site

I don't want to chance it yet as I have an active, production database.

Upvotes: 0

Views: 41

Answers (1)

Sandip - Frontend Developer
Sandip - Frontend Developer

Reputation: 14669

Use simple subquery:

UPDATE Prescription
SET active = 0 
WHERE patientid IN(SELECT id FROM patients WHERE [site] = @site)

OR

UPDATE Prescription
SET active = 0 
WHERE EXISTS (SELECT id FROM patients p WHERE p.id=Prescription.patientid AND p.[site] = @site)

Upvotes: 2

Related Questions