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
native_name_aliases table has fields
id, native_name, dpoint_id, building_id.
dpoint table has
dpoint_id, building_id, native_name and a bunch of other irrelevant fields.
Where am I going wrong?