CODEWITHSUNDEEP
sqlpostgresql
jmccartie
jmccartie

Reputation: 4976

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

Upvotes: 37

Views: 86173

Answers (8)

Olaoye Oluwapelumi
Olaoye Oluwapelumi

Reputation: 484

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.

Upvotes: 0

Nick S.
Nick S.

Reputation: 88

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

Upvotes: 5

Valery
Valery

Reputation: 61

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

Upvotes: 6

jturmel
jturmel

Reputation: 305

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

Upvotes: 1

mu is too short
mu is too short

Reputation: 434665

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.

Upvotes: 29

Luc M
Luc M

Reputation: 17314

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

Upvotes: 6

Jesse Dearing
Jesse Dearing

Reputation: 2271

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

Upvotes: -2

Emil Orol
Emil Orol

Reputation: 561

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

Upvotes: -3

Related Questions