update a column field using inner join two tables

问题:

UPDATE "TblFacultyMaster"
  SET "TblFacultyMaster".teacher_id = teacher_details.teacher_id
FROM teacher_details
  INNER JOIN "TblFacultyMaster" ON "TblFacultyMaster"."IMR"= teacher_details.primary_reg_no
WHERE ("TblFacultyMaster"."IMR" = teacher_details.primary_reg_no); 

I am getting this error

ERROR:  table name "TblFacultyMaster" specified more than once

回答1:

Quote from the manual

Note that the target table must not appear in the from_list, unless you intend a self-join (in which case it must appear with an alias in the from_list).

^{(Emphasis mine)}

So simple remove the inner join. You also need to remove the table prefix of the column to be updated on the left hand side of the SET:

UPDATE "TblFacultyMaster"
  SET teacher_id = teacher_details.teacher_id
FROM teacher_details
WHERE "TblFacultyMaster"."IMR" = teacher_details.primary_reg_no; 

回答2:

As @a_horse_with_no_name mentioned from the tutorial, the target table should not appear in the from list. You can however achieve this by aliasing the table in the from_list. I have done this before, and it works.