Error : ERROR: table name specified more than once

Question

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

Have you tried using an alias for `queued_items` in the `FROM` clause and the join conditions? — John Flatness, May 15 '11 at 00:33
Remove `queued_items.` from both parts of the `SET` clause, it's expecting the left sides to always be referring only to columns from the table you're updating. — John Flatness, May 15 '11 at 00:46

mu is too short · Accepted Answer · 2011-05-15T02:21:40.213

29

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.

edited May 15 '11 at 02:21

answered May 15 '11 at 00:47

mu is too short

426,620
70
833
800

Error : ERROR: column "queued_items" of relation "queued_items" does not exist – jmccartie May 15 '11 at 01:09
@jmccartie: I think I fixed the SQL. Please try my updated version. – mu is too short May 15 '11 at 02:22
Could you please explain why this works? – David Wesby Jun 22 '22 at 10:09
@DavidWesby Probably best to start with the docs: https://www.postgresql.org/docs/current/sql-update.html – mu is too short Jun 22 '22 at 16:37

score 6 · Answer 2 · answered Nov 30 '17 at 21:32

6

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

answered Nov 30 '17 at 21:32

Valery

61
1
1

The way I interpret the documentation on this: 'queued_items' immediately after the UPDATE is actually treated as an alias when there is also a FROM clause. Technically this query should work as UPDATE QI ... FROM queued_items as QI... So the error is says, "hey, that alias is already implicitly taken". Basically if we only have a where clause, then I don't have to fully qualify the field references with the table at all, but as soon as I introduce a From clause we need to introduce an alias for the table so that the compiler doesn't get confused. – Chris Schaller Nov 30 '17 at 21:58

score 6 · Answer 3 · answered May 15 '11 at 00:47

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

Nick S. · Answer 4 · 2019-05-03T09:30:49.507

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

score 1 · Answer 5 · answered May 15 '11 at 03:00

1

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

answered May 15 '11 at 03:00

jturmel

265
1
3
8

score 0 · Answer 6 · answered Jan 24 '22 at 10:05

0

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.

answered Jan 24 '22 at 10:05

Olaoye Oluwapelumi

426
5
8

score -2 · Answer 7 · answered May 15 '11 at 00:46

-2

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

answered May 15 '11 at 00:46

Jesse Dearing

2,251
18
20

1

Error : ERROR: relation "qi" does not exist – jmccartie May 15 '11 at 01:07

score -3 · Answer 8 · answered May 15 '11 at 00:19

-3

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

answered May 15 '11 at 00:19

Emil Orol

593
6
20

1

Removed the from line --> Error : ERROR: syntax error at or near "INNER" LINE 4: INNER JOIN users ON queued_items.user_id = users.imported_id (I'll update the post with that error) ^ – jmccartie May 15 '11 at 00:30

Error : ERROR: table name specified more than once

8 Answers8

Linked