SQL Postgres 错误：用作表达式的子查询返回多于一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21048955/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgres Error: More than one row returned by a subquery used as an expression
提问by user3182502
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
ERROR: more than one row returned by a subquery used as an expression
Any ideas?
有任何想法吗？
回答by Erwin Brandstetter
Technically, to repair your statement, you can add LIMIT 1to the subquery to ensure that at most 1 row is returned. That would remove the error, your code would still be nonsense.
从技术上讲，要修复您的语句，您可以添加LIMIT 1到子查询以确保最多返回 1 行。那将消除错误，您的代码仍然是无意义的。
... 'SELECT store_key FROM store LIMIT 1' ...Practically, you want to match rows somehowinstead of picking an arbitrary row from the remote table storeto update every row of your local table customer.
Your rudimentary question doesn't provide enough details, so I am assuminga text column match_namein both tables (and UNIQUEin store) for the sake of this example:
实际上，您希望以某种方式匹配行，而不是从远程表中选取任意行store来更新本地表的每一行customer。
你的基本问题没有提供足够的细节，所以为了这个例子，我假设match_name在两个表（和UNIQUEinstore）中都有一个文本列：
... '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 problemleading to your error is fixed.
It's almost always better to join in additional relations in the
FROMclause of anUPDATEstatementthan to run correlated subqueriesfor 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 foundin 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
WHEREclause prevents such empty updates:AND c.customer_id IS DISTINCT FROM sub.store_key
显然，导致您错误的基本问题已解决。
在语句的
FROM子句中UPDATE加入附加关系几乎总是比为每一行运行相关子查询更好。使用 dblink 时，上述内容变得重要一千倍。你不想调用
dblink()每一行，那是非常昂贵的。调用一次以检索您需要的所有行。对于相关子查询，如果在子查询中找不到行，则该列将更新为 NULL，这几乎总是不是您想要的。
在我更新的表单中，只有在找到匹配的行时才会更新该行。否则，不会触及该行。通常，当没有实际更改时，您不会想要更新行。这样做代价高昂（但仍然会产生死行）。
WHERE子句中的最后一个表达式可防止此类空更新：AND c.customer_id IS DISTINCT FROM sub.store_key
回答by peter.petrov
This means your nested SELECT returns more than one rows.
这意味着您的嵌套 SELECT 返回多行。
You need to add a proper WHERE clause to it.
您需要为其添加适当的 WHERE 子句。
回答by rotarydial
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:
在一对多关系的情况下，基本问题通常可以通过更改=to来简单解决IN。例如，如果您想更新或删除给定客户的一组帐户：
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
);
回答by dasblinkenlight
This error means that the SELECT store_key FROM storequery has returned two or more rows in the SERVER1database. 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.
此错误意味着SELECT store_key FROM store查询已返回SERVER1数据库中的两行或更多行。如果您想更新所有客户，请使用连接而不是标量=运算符。为了做到这一点，您需要一个条件来“连接”客户以存储物品。
If you wish to update all customer_ids to the same store_key, you need to supply a WHEREclause to the remotely executed SELECTso that the query returns a single row.
如果您希望将所有customer_ids更新为相同的store_key，则需要为WHERE远程执行的s提供一个子句，SELECT以便查询返回单行。
回答by user10101
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
查询产生的结果是没有需要正确处理的行，如果您在查询中提供有效的处理程序，则可以解决此问题，如 1. 限制查询返回单行 2. 这也可以通过提供“选择 max(column)" 将返回单行
