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

I have two separate databases. I am trying to update a column in one database to the values of a column from the other database:


UPDATE customer
SET customer_id=
(SELECT t1 FROM dblink('port=5432, dbname=SERVER1 user=postgres password=309245',
'SELECT store_key FROM store') AS (t1 integer));

This is the error I am receiving:

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


Any ideas?

This means your nested SELECT returns more than one rows.

You need to add a proper WHERE clause to it.

This error means that the `SELECT store_key FROM store` query has returned two or more rows in the `SERVER1` database. If you would like to update all customers, use a join instead of a scalar `=` operator. You need a condition to "connect" customers to store items in order to do that.

If you wish to update all `customer_id`s to the same `store_key`, you need to supply a `WHERE` clause to the remotely executed `SELECT` so that the query returns a single row.

The result produced by the Query is having no of rows that need proper handling this issue can be resolved if you provide the valid handler in the query like
1. limiting the query to return one single row
2. this can also be done by providing "select max(column)" that will return the single row

The fundamental problem can often be simply solved by changing an `=` to **`IN`**, in cases where you've got a one-to-many relationship. For example, if you wanted to update or delete a bunch of accounts for a given customer:

WITH accounts_to_delete AS
(
SELECT account_id
FROM accounts a
INNER JOIN customers c
ON a.customer_id = c.id
WHERE c.customer_name='Some Customer'
)

-- this fails if "Some Customer" has multiple accounts, but works if there's 1:
DELETE FROM accounts
WHERE accounts.guid =
(
SELECT account_id
FROM accounts_to_delete
);

-- this succeeds with any number of accounts:
DELETE FROM accounts
WHERE accounts.guid IN
(
SELECT account_id
FROM accounts_to_delete
);

**USE LIMIT 1 - so It will return only 1 row.**
Example

**customerId**- (select **id** from enumeration where enumerations.name = 'Ready To Invoice' limit 1)

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

<pre>... 'SELECT store_key FROM store <b>LIMIT 1</b>' ...</pre>

***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:

<pre>... 'SELECT store_key FROM store
<b>WHERE match_name = ' || quote_literal(customer.match_name) </b> ...</pre>

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][1] 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:

-

[To see links please register here]

[1]:

[To see links please register here]