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
                        native_name_aliases as nna
                    LEFT JOIN
                      dpoint as d
                      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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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