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

  • There is no way that this statement will delete any rows (unless there are triggers involved that do that). – ypercubeᵀᴹ Apr 6 '16 at 16:22
  • @ypercubeᵀᴹ: It would seem the OP actually meant "update the column with NULL" when (s)he wrote "delete". – Erwin Brandstetter Apr 6 '16 at 16:27
  • @ErwinBrandstetter yes, I guess you are right. – ypercubeᵀᴹ Apr 6 '16 at 16:28
  • i added an sql fiddle link. i hope it helps to understand. i just want to update id 2 and id 5 – fabvys Apr 6 '16 at 17:18
  • Problem in your fiddle: you have alias t2 for table_2 but still reference the original name which is not possible - the mismatch between table2 and table_2 aside. And your test needs to consider actual NULL values. I added an extended test case to my answer. – Erwin Brandstetter Apr 6 '16 at 18:25

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.

| improve this answer | |

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;
| improve this answer | |

Your Answer

