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

Question

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?

Solution 1

Technically, to repair your statement, you can add LIMIT 1 to the subquery to ensure that at most 1 row is returned. That would remove the error, your code 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.
Your rudimentary question doesn't provide enough details, so I am assuming a text column match_name in both tables (and 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 problem leading to your 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?

Solution 2

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 
);

Solution 3

This means your nested SELECT returns more than one rows.

You need to add a proper WHERE clause to it.

Solution 4

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_ids 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.

Solution 5

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

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

View more solutions

Comments

[Anoop P S]

thanks this saved my day, think a sub query with out limit cause the issue

[Kyle Laster]

I was not expecting to laugh out loud on stack overflow. "That would remove the error, your code would still be nonsense."