使用postgres中的内部联接两个表更新列字段，并且出现错误:表名"TblFacultyMaster"指定多次 [英] 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_details.primary_reg_no
WHERE ("TblFacultyMaster"."IMR" = teacher_details.primary_reg_no); 

我收到此错误

ERROR:  table name "TblFacultyMaster" specified more than once

推荐答案

从手册中引用

请注意，除非您打算进行自联接，否则目标表必须不出现在from_list中.(在这种情况下，目标表必须以别名出现在from_list中).

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

^{(强调我的)}

如此简单地删除inner join.您还需要删除SET:

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; 

这篇关于使用postgres中的内部联接两个表更新列字段，并且出现错误:表名"TblFacultyMaster"指定多次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！