CODEWITHSUNDEEP
sqlselect
user1657563
user1657563

Reputation: 237

Update a column in table based on column from another table

I have two tables. Patients Table has PatientName, Age, and Sex. Visits Table has PatientName, SpecialistName, DateOfVisit, and an Age Column which doesn't have any values in it yet. I want to update the Age Column in the Visits Table based on that patient's age from the Patients Table. Here is my best guess, but the message I receive is:

"Operation must use an updateable query."

update visits v
set v.age = (
select p.age from
patients p where v.pname = p.pname);

Upvotes: 0

Views: 122

Answers (3)

Vijaychandar
Vijaychandar

Reputation: 714

Its simple 

update visits v, patients p set v.age = p.age where v.pname = p.pname;

Upvotes: 0

John Woo
John Woo

Reputation: 263693

If you are using MSSQL

UPDATE v
SET v.Age = p.Age
FROM visits V INNER JOIN patients p
ON v.pname = p.pname

If you are using MySQL

UPDATE visits V
INNER JOIN patients p
ON v.pname = p.pname
SET v.Age = p.Age

See this Link: UPDATE with JOIN

Upvotes: 1

Robert
Robert

Reputation: 25753

Try to use from clause as below:

update visits v
set v.age = p.age
from patients p 
where v.pname = p.pname;

Upvotes: 0

Related Questions