how to make an update between two tables in postgresql-CodePudding

i have a generalization named Personal, and the especialization is named Teacher. The generalization have a name and the teacher an idTeacher (tables have a relation).

I want to update the name of the personal but only if it matches with the teacherId.

CREATE TABLE PERSONAL(
name VARCHAR(45) NOT NULL,
idPERSONAL serial PRIMARY KEY);

CREATE TABLE TEACHER(
couse VARCHAR(45) NOT NULL,
idTeacher serial PRIMARY KEY,
idPERSONAL FOREIGN KEY REFERENCES FROM(PERSONAL));

i tried

UPDATE PERSONAL SET name='newName' FROM TEACHER WHERE idTeacher = '1';

And this works but update me all the members of the personal, not only the teacher 1; how can i just update the teacher 1?

CodePudding user response：

Try this.

UPDATE PERSONAL
SET name = 'newName'
WHERE idPERSONAL IN (SELECT idPERSONAL FROM TEACHER WHERE idTeacher = '1');

CodePudding user response：

Like with a join, you need to specify how to join the tables.

update personal
set name = 'newname'
from teacher
where teacher.idPersonal = personal.idPersonal
  and idTeacher = 1

There are many examples of different ways to join in the Postgresql update documentation.