我有两个单独的数据库。我正在尝试将一个数据库中的列更新为另一数据库中的列的值:

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

这是我收到的错误:



有任何想法吗?

最佳答案

从技术上来说是,要修复您的语句,可以将 LIMIT 1 添加到子查询中,以确保最多返回1行。那将消除错误,您的代码仍然是胡说八道。

... 'SELECT store_key FROM store LIMIT 1' ...

实际上,您希望以某种方式匹配行,而不是从远程表store中选择任意行以更新本地表customer的每一行。
您的基本问题没有提供足够的详细信息,因此对于此示例,我假设是(假设是两个表中的文本列match_name(和UNIQUE中的store)):
... 'SELECT store_key FROM store
     WHERE match_name = ' || quote_literal(customer.match_name)  ...

但这是一种极其昂贵的工作方式。

理想情况下为,您应该完全重写语句
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;

这可以解决您原始声明中的许多问题。
  • 显然,导致您的错误的基本问题已修复。
  • FROM clause of an UPDATE statement中加入其他关系总是比为每行运行相关子查询总是更好。
  • 使用dblink时,以上内容变得重要一千倍。您不想为每一行都调用dblink(),这是极其昂贵的。调用一次即可检索所需的所有行。
  • 对于相关的子查询,如果在子查询中未找到找不到,则该列将更新为NULL,这几乎始终不是您想要的。
    在我的更新表格中,只有找到匹配的行,该行才会更新。否则,该行不受影响。
  • 通常,当实际上没有任何更改时,您不想更新行。什么都不做代价很高(但是仍然会产生死行)。 WHERE子句中的最后一个表达式可防止此类空更新:
     AND   c.customer_id IS DISTINCT FROM sub.store_key
    
  • 关于sql - Postgres错误:子查询返回的多于一行用作表达式,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/21048955/

    10-11 23:08
    查看更多