Postgres Error: More than one row returned by a subquery used as an expression

database, dblink, postgresql, sql, subquery

Solution

Technically, to remove the error, add `LIMIT 1` to the subquery to return at most 1 row. The statement would still be nonsense.

... 'SELECT store_key FROM store LIMIT 1' ...

Practically, you want to match rows somehow instead of picking an arbitrary row from the remote table `store` to update every row of your local table `customer`. I assume a text column `match_name` in both tables (`UNIQUE` in `store`) for the sake of this example:

... 'SELECT store_key FROM store
     WHERE match_name = ' || quote_literal(customer.match_name)  ...

But that's an extremely expensive way of doing things.

Ideally, you completely rewrite the statement.

UPDATE customer c
SET    customer_id = s.store_key
FROM   dblink('port=5432, dbname=SERVER1 user=postgres password=309245'
            , 'SELECT match_name, store_key FROM store')
       AS s(match_name text, store_key integer)
WHERE c.match_name = s.match_name
AND   c.customer_id IS DISTINCT FROM s.store_key;

This remedies a number of problems in your original statement.

Obviously, the basic error is fixed.

It's typically better to join in additional relations in the `FROM` clause of an `UPDATE` statement than to run correlated subqueries for every individual row.

When using dblink, the above becomes a thousand times more important. You do not want to call `dblink()` for every single row, that's extremely expensive. Call it once to retrieve all rows you need.

With correlated subqueries, if no row is found in the subquery, the column gets updated to NULL, which is almost always not what you want. In my updated query, the row only gets updated if a matching row is found. Else, the row is not touched.

Normally, you wouldn't want to update rows, when nothing actually changes. That's expensively doing nothing (but still produces dead rows). The last expression in the `WHERE` clause prevents such empty updates:

     AND   c.customer_id IS DISTINCT FROM sub.store_key

Related:

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

Problem

Original source

Related problems

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