postgresql 通过引用另一个表来更新一个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6728102/
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
Updating a table by referencing another table
提问by InvalidBrainException
I have a table CustPurchase(name, purchase) and another table CustID(id, name).
我有一张桌子CustPurchase（姓名，购买）和另一张桌子CustID（身，姓名）。
I altered the CustPurchasetable to have an idfield. Now, I want to populate this newly created field by referencing the customer ids from the CustIDtable, using:
我改变了CustPurchase表格以有一个id字段。现在，我想通过引用CustID表中的客户 ID 来填充这个新创建的字段，使用：
UPDATE CustPurchase
SET CustPurchase.id = CustID.id
WHERE CustPurchase.name = CustID.name;
I keep getting syntax errors!
我不断收到语法错误！
回答by Brian Webster
I believe you are after the useful UPDATE FROMsyntax.
我相信你在追求有用的UPDATE FROM语法。
UPDATE CustPurchase SET id = CI.id
FROM
CustPurchase CP
inner join CustID CI on (CI.name = CP.name)
This might have to be the following:
这可能必须是以下内容：
UPDATE CustPurchase SET id = CI.id
FROM
CustID CI
WHERE
CI.name = CustPurchase.name
Sorry, I'm away from my Postgres machine; however, based upon the reference, it looks like this is allowable. The trouble is whether or not to include the source table in the from_list.
抱歉，我不在 Postgres 机器旁边；但是，根据参考资料，看起来这是允许的。麻烦在于是否将源表包含在from_list 中。
回答by OMG Ponies
Joining by name is not an ideal choice, but this should work:
按名称加入不是一个理想的选择，但这应该有效：
UPDATE custpurchase
SET id = (SELECT c.id
FROM CUSTID c
WHERE c.name = custpurchase.name)
The caveat is that if there's no match, the value attempting to be inserted would be NULL. Assuming the idcolumn won't allow NULLbut will allow duplicate values:
需要注意的是，如果没有匹配项，则尝试插入的值将是NULL. 假设该id列不允许NULL但允许重复值：
UPDATE custpurchase
SET id = (SELECT COALESCE(c.id, -99)
FROM CUSTID c
WHERE c.name = custpurchase.name)
COALESCEwill return the first non-NULL value. Making this a value outside of what you'd normally expect will make it easier to isolate such records & deal with appropriately.
COALESCE将返回第一个非 NULL 值。将此值设置为超出您通常期望的值将更容易隔离此类记录并进行适当处理。
Otherwise, you'll have to do the updating "by hand", on a name by name basis, to correct instances that SQL could not.
否则，您必须逐个名称地“手动”进行更新，以纠正 SQL 无法纠正的实例。
