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

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 doesnt 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 thats 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.

Its 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, thats 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 wouldnt want to update rows, when nothing actually changes. Thats 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

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

The fundamental problem can often be simply solved by changing an = to IN, in cases where youve 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 theres 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 
);

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

This means your nested SELECT returns more than one rows.

You need to add a proper WHERE clause to it.

Related posts on SQL :

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

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

Leave a Reply Cancel reply