Error: ERROR: table name specified more than once

Question

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

Trying to grab import_id from other tables and update. Here is 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 to delete 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 clauses

 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 

Received 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 subquery:

 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 he is crazy slow

Answer 1

Try the following:

 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 

Yes, the old conditions for school.

Answer 2

From 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 that allows columns from other tables to make a WHERE clause and update an expression. This is like a list of tables that can be specified in the FROM SELECT clause of an expression. Please note that the target table should not appear in the from_list if you do not intend to join (in which case it should appear with an alias in from_list).

Answer 3

 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 

Answer 4

Use the subquery statement and add indexes to these columns.

Answer 5

I had to play around with the names of the columns and tables, and in the end it worked. I had to:

• leave the table name in the destination columns of the SET
• make sure that the table alias is updated

Your equivalent will 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:

 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 

Answer 6

You should be able to change the name after UPDATE to an alias. You can also use aliases in the set clause. This means that you can also install them in your JOIN offers.

 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 

Answer 7

You do not need a FROM clause. Remove "FROM queued_items" and you're done.