引用另一个表更新一张表

Question

我有一张表CustPurchase（名称，购买），还有另一张表CustID（id，名称）。

我修改了CustPurchase表，添加了一个id字段。现在，我想通过引用CustID表中的客户ID来填充这个新创建的字段，使用如下语句：

UPDATE CustPurchase
   SET CustPurchase.id = CustID.id 
 WHERE CustPurchase.name = CustID.name;

我一直遇到语法错误！

- InvalidBrainException

Answer 1

我相信你想要的是有用的UPDATE FROM语法。

UPDATE CustPurchase SET id = CI.id 
FROM
   CustPurchase CP
   inner join CustID CI on (CI.name = CP.name)

可能需要这个：

UPDATE CustPurchase SET id = CI.id 
FROM
   CustID CI 
WHERE
   CI.name = CustPurchase.name

抱歉，我离开了我的Postgres机器；但是，根据参考文献，这似乎是允许的。问题是是否要在from_list中包含源表。

- Brian Webster

---

那看起来像是MySQL的语法 - PostgreSQL似乎不支持UPDATE语句中的JOIN操作：http://www.postgresql.org/docs/9.0/static/sql-update.html - OMG Ponies

@ponies：实际上是这样的。任何跟踪目标行的ctid的选择语句都可以正常工作 - 后者排除了例如group by或递归with等情况。 - Denis de Bernardy

我认为这是允许的。从参考文献中： "from_list一个表达式列表，允许在 WHERE 条件和更新表达式中出现来自其他表的列。这类似于可以在 SELECT 语句的 FROM 子句中指定的表列表。请注意，目标表不得出现在 from_list 中，除非您打算进行自连接（在这种情况下，必须使用别名在 from_list 中出现）。" 然而，显然不应该在 FROM 列表中包括源表。 - Brian Webster

MySQL也有类似的问题，但它允许JOIN而不是子查询 :/ - OMG Ponies

我认为在SQL Server中，我被迫在FROM子句中包含源表，但有时这些“事实”最终证明是我多年前做出的奇怪假设。 - Brian Webster

Answer 2

按名称加入不是一个理想的选择，但这应该能够正常工作：

UPDATE custpurchase
   SET id = (SELECT c.id
               FROM CUSTID c
              WHERE c.name = custpurchase.name)

需要注意的是，如果没有匹配项，那么尝试插入的值将会是NULL。假设id列不允许NULL但允许重复的值：

UPDATE custpurchase
   SET id = (SELECT COALESCE(c.id, -99)
               FROM CUSTID c
              WHERE c.name = custpurchase.name)

COALESCE将返回第一个非NULL值。把它设为一个通常不会出现的值，可以更容易地隔离这些记录并进行适当处理。

否则，您将不得不手动逐个更新名称，以纠正SQL无法处理的实例。

- OMG Ponies

---

我永远无法理解SQL中的嵌套查询。它们似乎比典型编程语言中的嵌套更没有意义。如果我看到那个查询，我会将其翻译成英语：“从表CUSTID获取id，其中CUSTID.name等于Custpurchase.name。然后将所有id分配给custpurchase中的id字段”，这没有意义。无论如何，我刚刚在大约90,000条记录的两个表上运行了您的查询，到目前为止已经过去了3分钟。我怎么知道Postgres没有冻结？哈。 - InvalidBrainException