'Error : ERROR: table name specified more than once

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

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

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:

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

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)

PS Trying to avoid this sub-query:

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

sqlpostgresql


Solution 1:[1]

From postgres site

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

Solution 2:[2]

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

Solution 3:[3]

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 SET columns
  • ensure that I aliased the table being updated

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

Solution 4:[4]

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

Solution 5:[5]

Don't know if this helps. In my case, what happened was that I eager loaded the data and also specified it as data to be joined in relations.

Solution 6:[6]

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

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Luc M
Solution 2 Valery
Solution 3
Solution 4 jturmel
Solution 5 Olaoye Oluwapelumi
Solution 6 Jesse Dearing

Related Questions