PostgreSQL数据库-内部联接查询错误 [英] PostgreSQL Database - Inner Join Query Error

问题描述

在这个社区的帮助下，我能够使用内部联接产生查询，我认为这种内部联接可以满足我的尝试。不幸的是，当我尝试运行下面的查询时，收到以下错误：

With the help of this community, I was able to produce a query using an inner join which I thought would work for what I'm trying to do. Unfortunately, when I attempted to run the query found below, I received the following error:

错误：表名 bue指定了多次

ERROR: table name "bue" specified more than once

根据我在Google上阅读的内容，有人说不需要 FROM bue，但是当我删除它时，我在下面找到另一个错误：

From what I've read on Google, some people have said that the "FROM bue" is not needed, but when I removed this, I got another error found below:

错误：字符98处 INNER处或附近的语法错误

ERROR: syntax error at or near "INNER" at character 98

非常感谢您协助我们进行故障排除。

I'd very much appreciate your assistance in troubleshooting this. Thank you so very much.

查询：

UPDATE
  bue
SET
  rgn_no = chapterassociation.rgn_no,
  chp_cd = chapterassociation.chp_cd
FROM
  bue
INNER JOIN
  chapterassociation
    ON  bue.work_state = chapterassociation.work_state
    AND bue.bgu_cd = chapterassociation.bgu_cd
WHERE
  bue.mbr_no IS NULL AND bue.chp_cd IS NULL

推荐答案

在PostgreSQL中，将表指定为更新仅需要在UPDATE子句中完成，例如 UPDATE bue 。 FROM子句仅适用于查询中引用的附加表。 （如果您要在 bue 上进行自联接，则可以在FROM子句中再次提及它，但是在这种情况下就不行了。）

In PostgreSQL, specifying the table to be updated needs to be done only in the UPDATE clause, e.g. UPDATE bue. The FROM clause is only for additional tables referenced in the query. (If you were doing a self-join on bue, you would mention it again in the FROM clause, but you aren't in this case.)

第二个错误可能只是一个简单的语法错误。另一个棘手的事情是JOIN / ON语法不适合FROM子句，因此您必须将联接条件移到WHERE子句。尝试类似的操作：

The second error you get is likely just a simple syntax error. The other tricky thing is that JOIN/ON syntax doesn't fit in the FROM clause, so you have to move the join conditions to the WHERE clause. Try something like:

UPDATE
  bue
SET
  rgn_no = chapterassociation.rgn_no,
  chp_cd = chapterassociation.chp_cd
FROM
  chapterassociation
WHERE
  bue.mbr_no IS NULL AND bue.chp_cd IS NULL
  AND bue.work_state = chapterassociation.work_state
  AND bue.bgu_cd = chapterassociation.bgu_cd

请参见 http://www.postgresql.org/docs/current/interactive/sql-update.html

（注意，至少我不知道如何将JOIN / ON放入UPDATE语句中……我可能会丢失一些东西。）

(N.B. At least I don't know how to put JOIN/ON into an UPDATE statement... I could be missing something.)

这篇关于PostgreSQL数据库-内部联接查询错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！