psycopg2.errors.UndefinedTable: relation does not exist (join table)-CodePudding

I'm trying to replicate this answer using postgreSQL and psycopg2: https://stackoverflow.com/a/33632820/1200914

My SQL code looks like (I just put the new line here for better readibility):

UPDATE t SET mycolumn = a.mycolumn FROM mytable AS t INNER JOIN (VALUES (28625, '1'),
                        (56614, '1'),  (86517, '1') ) AS a(id, mycolumn) ON a.id = t.id

However, I'm getting the next error:

psycopg2.errors.UndefinedTable: relation "t" does not exist

when executing this sql with my cursor. In mytable I have a column with the name mycolumn and another one with name id, which is the primary key. What am I missing? By the way, the order of the couples should be like this, no? Just asking because in the previous answer I think the user exchanged the id and value values.

CodePudding user response：

To get it to work I reworked the query as:

UPDATED. Added WHERE clause.

UPDATE
    mytable
SET
    mycolumn = a.mycolumn::boolean
FROM
    mytable AS t
    INNER JOIN (
        VALUES (28625, '1'),
            (56614, '1'),
            (86517, '1')) AS a (id, mycolumn) ON a.id = t.id
    WHERE
        a.id = mytable.id
;

When I tried your original query I got:

ERROR: table name "t" specified more than once

When I tried my comment suggestion I got:

ERROR: column reference "id" is ambiguous

The docs from here UPDATE are somewhat confusing:

alias

A substitute name for the target table. When an alias is provided, it completely hides the actual name of the table. For example, given UPDATE foo AS f, the remainder of the UPDATE statement must refer to this table as f not foo.

from_item

A table expression allowing columns from other tables to appear in the WHERE condition and update expressions. This uses the same syntax as the FROM clause of a SELECT statement; for example, an alias for the table name can be specified. Do not repeat the target table as a from_item unless you intend a self-join (in which case it must appear with an alias in the from_item).

But given the error messages I figured the UPDATE portion needed the actual table name and the FROM needed the aliased name.