1

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

5
  • There is no way that this statement will delete any rows (unless there are triggers involved that do that). Apr 6, 2016 at 16:22
  • @ypercubeᵀᴹ: It would seem the OP actually meant "update the column with NULL" when (s)he wrote "delete". Apr 6, 2016 at 16:27
  • @ErwinBrandstetter yes, I guess you are right. Apr 6, 2016 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, 2016 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. Apr 6, 2016 at 18:25

2 Answers 2

4

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.

0
0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.