postgresql 将一列从一个表插入到另一个有约束的表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10923518/
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
insert a column from one table to another with constraints
提问by Rahul Desai
I have 2 tables: legendand temp. temp has an ID and account ID, legend has advent_id, account_id and other columns. ID in temp and advent_id in legend are similar. account_id column is empty and I want to import the relevant account_id from temp to legend. I am using PostgreSQL.
我有 2 个表：legend和temp。temp 有 ID 和 account ID，legend 有 advent_id、account_id 等列。temp 中的 ID 和图例中的 advent_id 相似。account_id 列是空的，我想将相关的 account_id 从 temp 导入到图例。我正在使用 PostgreSQL。
I am trying the following query, but it is not working as I am expecting. New rows are getting created and the account_id's are getting added in the new rows, not to the corresponding advent_id.
我正在尝试以下查询，但它没有按我的预期工作。新行被创建，account_id 被添加到新行中，而不是相应的 advent_id。
insert into legend(account_id)
select temp.account_id
from legend, temp
where legend.advent_id=temp.id;
It is inserting the account_id in the wrong place, not to the corresponding advent_id.
它是在错误的地方插入了 account_id，而不是对应的 advent_id。
I am using the following query to check:
我正在使用以下查询来检查：
select advent_id, account_id from legend where account_id is not null;
What exactly is the problem in my insert query?
我的插入查询到底有什么问题？
回答by gcbenison
If you are trying to modify existing rows, rather than add rows, then you need an UPDATEquery, not an INSERTquery.
如果您尝试修改现有行，而不是添加行，那么您需要UPDATE查询，而不是INSERT查询。
It's possible to use joined tables as the target of an UPDATE; in your example:
可以使用连接表作为UPDATE;的目标。在你的例子中：
UPDATE legend
JOIN temp
SET legend.account_id = temp.account_id
WHERE(temp.id = legend.advent_id);
