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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

This means your nested SELECT returns more than one rows    You need to add a proper WHERE clause to it

User · Answer

USE LIMIT 1 - so It will return only 1 row  Example customerId-  select id from enumeration where enumerations name    Ready To Invoice  limit 1

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

Examples related to sql

Examples related to database

Examples related to postgresql

Examples related to subquery

Examples related to dblink