[sql] 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 question is related to sql database postgresql subquery dblink

The answer is


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 should 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 almost always 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 form, 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
    

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)


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


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


Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to database

Implement specialization in ER diagram phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' cannot be loaded Room - Schema export directory is not provided to the annotation processor so we cannot export the schema SQL Query Where Date = Today Minus 7 Days MySQL Error: : 'Access denied for user 'root'@'localhost' SQL Server date format yyyymmdd How to create a foreign key in phpmyadmin WooCommerce: Finding the products in database TypeError: tuple indices must be integers, not str

Examples related to postgresql

Subtracting 1 day from a timestamp date pgadmin4 : postgresql application server could not be contacted. Psql could not connect to server: No such file or directory, 5432 error? How to persist data in a dockerized postgres database using volumes input file appears to be a text format dump. Please use psql Postgres: check if array field contains value? Add timestamp column with default NOW() for new rows only Can't connect to Postgresql on port 5432 How to insert current datetime in postgresql insert query Connecting to Postgresql in a docker container from outside

Examples related to subquery

What is the difference between LATERAL and a subquery in PostgreSQL? Postgres Error: More than one row returned by a subquery used as an expression How does Subquery in select statement work in oracle Difference between Subquery and Correlated Subquery How to do this in Laravel, subquery where in SQL LEFT JOIN Subquery Alias Subquery returned more than 1 value.This is not permitted when the subquery follows =,!=,<,<=,>,>= or when the subquery is used as an expression subquery in FROM must have an alias Is there a performance difference between CTE , Sub-Query, Temporary Table or Table Variable? How can I insert values into a table, using a subquery with more than one result? Postgres Error: More than one row returned by a subquery used as an expression How to create a DB link between two oracle instances How to SELECT in Oracle using a DBLINK located in a different schema? postgresql: INSERT INTO ... (SELECT * ...) How to use (install) dblink in PostgreSQL?