Update A Column Field Using Inner Join Two Tables In Postgres And I Am Getting An Error: Table Name "tblfacultymaster" Specified More Than Once

UPDATE 'TblFacultyMaster' SET 'TblFacultyMaster'.teacher_id = teacher_details.teacher_id FROM teacher_details INNER JOIN 'TblFacultyMaster' ON 'TblFacultyMaster'.'IMR'= teacher

Solution 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).

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; 

Solution 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.