UPDATE a column in one table according to a column in another In Postgres, I have two tables that have become de-normalized. We're in the process of migrating to a normalized form, but in the meantime the tables have become out of sync. I'd like to copy the values for the native_name column from the native_name_aliases table into the dpoint table. Here is my attempt:

update dpoint
set native_name = (select
                      nna.native_name
                    from
                        native_name_aliases as nna
                    LEFT JOIN
                      dpoint as d
                    ON
                      d.dpoint_id = nna.dpoint_id)
where building_id = 42;


I get an error message:


  more than one row returned by a subquery used as an expression


My native_name_aliases table has fields id, native_name, dpoint_id, building_id.
And the dpoint table has dpoint_id, building_id, native_name and a bunch of other irrelevant fields.

Where am I going wrong?
Your subquery is uncorrelated and returns all rows from the join between native_name_aliases and dpoint, where only a single row would be allowed. I guess this is what you are after:

UPDATE dpoint d
SET    native_name = nna.native_name
FROM   native_name_aliases nna
WHERE  d.building_id = 42
AND    d.dpoint_id = nna.dpoint_id
AND    d.native_name IS DISTINCT FROM nna.native_name;


You did not provide table definitions and Postgres version, so I can't tell for sure.

The last line is to prevent expensive "empty" updates. Details:


How do I (or can I) SELECT DISTINCT on multiple columns?