UPDATE from subquery updates rows that shouldn't be updated

Question

Using PostgreSQL, I want to update a column. The information how to update is in another table.

My try was this, but it also updates rows I don't want to update:

update table_1
set column_x = (
    select table_2.rename
    from table_2    
    where table_2.rename IS NOT NULL 
        and table_1.id = table_2.id)

How to modify the query?

Maybe this helps to explain my problem: http://www.sqlfiddle.com/#!15/318fb/1

Answer 1

Use a FROM clause to join to more tables:

UPDATE table_1 t1    -- alias optional
SET    column_x = t2.rename
FROM   table_2 t2
WHERE  t2.rename IS NOT NULL  -- assuming you actually meant t1
AND    t2.id = t1.id;

This joins rows from table_2 in a CROSS JOIN - filtered by the condition t2.id = t1.id which makes it an INNER JOIN effectively.

This way, rows without a match in the other table are not touched at all - unlike your query with a correlated subquery.

SQL Fiddle with extended demo.

You must also understand the difference between NULL, empty string and "no row" (which results in NULL in your correlated subquery).

Details in the manual.

Answer 2

thanks for the help. worked with the following ...

UPDATE table1
SET    value = t2.rename
FROM   table2 t2
WHERE  t2.rename IS NOT NULL 
AND    id = t2.id;