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

Question

I'm trying to replicate this answer using postgreSQL and psycopg2: https://mcmap.net/q/970250/-is-it-possible-to-update-rows-from-a-key-value-pair

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.

Agace

Per the docs UPDATE: 'Do not include the table's name in the specification of a target column — for example, UPDATE table_name SET table_name.col = 1 is invalid'. So ON a.id = id. – Quinidine

It prompts the same error... – Agace

Answer 1

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.

Quinidine

did you check the results on the rows too? I accepted because it wasn't giving me any error, and had logic, but now I'm seeing that it's applying the boolean value to all rows of the table instead of only those rows selected (basically I only want to set True on some rows) – Agace

See my UPDATE that adds WHERE clause. If the mycolumn field is not actually boolean then eliminate the ::boolean cast. That was an assumption on my part. – Quinidine