SQL 错误：错误：多次指定表名

Error : ERROR: table name specified more than once

提问by jmccartie

I have a table "queued_items". The current "user_id" and "item_id" are incorrect, but are stored in the other tables: users.imported_id and items.imported_id

我有一个表“queued_items”。当前的“user_id”和“item_id”不正确，但存储在其他表中：users.imported_id 和 items.imported_id

Trying to grab the imported_id from the other tables and update. Here's what I tried

试图从其他表中获取imported_id 并更新。这是我尝试过的

UPDATE queued_items
SET queued_items.user_id = users.id,
    queued_items.item_id = items.id
FROM queued_items
INNER JOIN users ON queued_items.user_id = users.imported_id
INNER JOIN items ON queued_items.item_id = items.imported_id

Getting this error:

收到此错误：

Error : ERROR:  table name "queued_items" specified more than once

Tried removing the FROM line, got this error:

尝试删除 FROM 行，出现此错误：

Error : ERROR:  syntax error at or near "INNER"
LINE 4: INNER JOIN users ON queued_items.user_id = users.imported_id
         ^

I also tried adding an alias to the FROM and JOIN conditions

我还尝试为 FROM 和 JOIN 条件添加别名

UPDATE queued_items
SET queued_items.user_id = users.id,
    queued_items.item_id = items.id
FROM queued_items as qi
INNER JOIN users ON qi.user_id = users.imported_id
INNER JOIN items ON qi.item_id = items.imported_id

Got this error:

得到这个错误：

Error : ERROR:  column "queued_items" of relation "queued_items" does not exist
LINE 2: SET queued_items.user_id = users.id,
            ^

Any ideas? (postgres 9)

有任何想法吗？(postgres 9)

PSTrying to avoid this sub-query:

PS试图避免这个子查询：

UPDATE queued_items
SET user_id = (SELECT id FROM users WHERE queued_items.user_id = users.imported_id),
    item_id = (SELECT id FROM items WHERE queued_items.item_id = items.imported_id)

...because it's crazy slow

...因为它太慢了

采纳答案by mu is too short

Try this:

尝试这个：

UPDATE queued_items
SET user_id = users.id,
    item_id = items.id
FROM users, items
WHERE queued_items.user_id = users.imported_id
  AND queued_items.item_id = items.imported_id

Yeah, old school join conditions.

是的，老派的加入条件。

回答by Luc M

From postgres site

来自 postgres 站点

UPDATE [ ONLY ] table [ [ AS ] alias ]
    SET { column = { expression | DEFAULT } |
          ( column [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]
    [ FROM from_list ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

*from_list*

*来自_列表*

A list of table expressions, allowing columns from other tables to appear in the WHERE condition and the update expressions. This is similar to the list of tables that can be specified in the FROM Clause of a SELECT statement. 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).

表表达式列表，允许其他表中的列出现在 WHERE 条件和更新表达式中。这类似于可以在 SELECT 语句的 FROM 子句中指定的表列表。请注意，目标表不得出现在 from_list 中，除非您打算进行自联接（在这种情况下，它必须以别名出现在 from_list 中）。

回答by Valery

UPDATE queued_items
SET user_id = users.id,
    item_id = items.id
FROM queued_items as QI
INNER JOIN users ON QI.user_id = users.imported_id
INNER JOIN items ON QI.item_id = items.imported_id

回答by Nick S.

I had to play around with the column/table naming and eventually got it to work. I had to:

我不得不尝试使用列/表命名并最终让它工作。我不得不：

• leave out the table name in the destination SETcolumns
• ensure that I aliased the table being updated

• 在目标SET列中省略表名
• 确保我为正在更新的表设置了别名

Your equivalent would be:

你的等价物是：

UPDATE queued_items
SET user_id = users.id,
    item_id = items.id
FROM queued_items as alias_queued_items
INNER JOIN users ON alias_queued_items.user_id = users.imported_id
INNER JOIN items ON alias_queued_items.item_id = items.imported_id

instead of:

代替：

UPDATE queued_items
SET queued_items.user_id = users.id,
    queued_items.item_id = items.id
FROM queued_items
INNER JOIN users ON queued_items.user_id = users.imported_id
INNER JOIN items ON queued_items.item_id = items.imported_id

回答by jturmel

Use the sub-query statement and add indexes to those columns.

使用子查询语句并向这些列添加索引。

回答by Jesse Dearing

You should be able to change the name after the UPDATEto the alias. Also you can use the aliased names in the set clause. This means that you can set them in your JOINclauses as well.

您应该能够UPDATE在别名之后更改名称。您也可以在 set 子句中使用别名。这意味着您也可以在JOIN子句中设置它们。

UPDATE qi 
SET qi.user_id = us.id,
    qi.item_id = itms.id
FROM queued_items qi
INNER JOIN users us ON qi.user_id = us.imported_id
INNER JOIN items itms ON qi.item_id = itms.imported_id

回答by Emil Orol

You don't need the FROM clause. Remove "FROM queued_items" and you are done.

您不需要 FROM 子句。删除“FROM queued_items”，你就完成了。