UPDATE from another table with multiple WHERE criteria

Question

In Postgres 9.5, I want to connect to another DB using Postgres' dblink, get data and then use them to update another table.

-- connect to another DB, get data from table, put it in a WITH 
WITH temp_table  AS 
( 
    SELECT r_id, descr,  p_id 
    FROM 
    dblink('myconnection', 
           'SELECT 
            r_id, descr,   p_id 
            FROM table
            WHERE table.p_id 
            IN (10,20);'
    ) 
    AS tempTable(r_id integer, descr text, p_id integer)
) 

-- now use temp_table  to update

UPDATE anothertable   
SET     
descr =temp_table.descr 
FROM anothertable    AS x
INNER JOIN temp_table 
ON  
x.r_id = temp_table.r_id 
AND 
x.p_id = temp_table.p_id 
AND 
x.p_id IN (2)  ;

dblink works fine and if I do select * from temp_table before the UPDATE, it has data.

The issue is the UPDATE itself. It runs with no errors, but it never actually updates the table.

I tried changing the UPDATE to:

UPDATE anothertable   
SET     
descr =temp_table.descr 
FROM anothertable    AS x , temp_table
WHERE x.r_id = temp_table.r_id 
AND 
x.p_id = temp_table.p_id 
AND 
x.p_id IN (2)  ;

Same as above: runs with no errors, but it never actually updates the table.

I also tried to change the UPDATE to:

UPDATE anothertable   
INNER JOIN temp_table
ON x.r_id = temp_table.r_id 
    AND 
    x.p_id = temp_table.p_id 
    AND 
    x.p_id IN (2) 
    SET descr =temp_table.descr

But I get:

ERROR: syntax error at or near "INNER" SQL state: 42601 Character: 1894

How can I fix this to actually update?

Aside: Postgres 9.5 has reached EOL in 2021. Consider upgrading to a current version! https://www.postgresql.org/support/versioning/ — Erwin Brandstetter, Mar 14 '22 at 15:54

Erwin Brandstetter · Answer 1 · 2022-03-14T15:35:16.080

Don't repeat the target table in the FROM clause of the UPDATE:

WITH temp_table AS ( ... )

UPDATE anothertable x
SET    descr = t.descr 
FROM   temp_table t
WHERE  x.r_id = t.r_id 
AND    x.p_id = t.p_id 
AND    x.p_id IN (2);

Or simplified:

...
AND    x.p_id = 2
AND    t.p_id = 2

The manual:

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

UPDATE from another table with multiple WHERE criteria

1 Answers1