如何将数据从某个表中的一列复制到SQL Server中另一个表中的同一命名列

I have two SQL Server tables sub_aminer_author2paper and sub_aminer_paper, and there is a common column pid.

我有两个SQL Server表sub_aminer_author2paper和sub_aminer_paper，并且有一个公共列pid。

Another column aid exists in only one table i.e. sub_aminer_author2paper.

另一个列辅助仅存在于一个表中，即sub_aminer_author2paper。

Now I have to copy column aid from table sub_aminer_author2paper to newly created column aid in the table sub_aminer_paper, whereas the column pid in table sub_aminer_paper should match the pid in table sub_aminer_author2paper

现在我必须将表sub_aminer_author2paper中的列辅助复制到表sub_aminer_paper中新创建的列辅助，而表sub_aminer_paper中的列pid应该与表sub_aminer_author2paper中的pid匹配

I have tried this query as:

我试过这个查询：

insert into sub_aminer_paper (sub_aminer_paper.aid) 
    (select sub_aminer_author2paper.aid  
     from sub_aminer_author2paper 
     INNER JOIN sub_aminer_paper ON sub_aminer_paper.pid = sub_aminer_author2paper.pid)

But it not works as required, it has inserted all the aid values at end of the table as

但它不能按要求工作，它已将所有辅助值插入到表的末尾

Please help!

请帮忙！

2 个解决方案

#1

Add a column named aid with null in sub_aminer_paper table. Then simply update it.

在sub_aminer_paper表中添加名为aid with null的列。然后只需更新它。

UPDATE A
SET A.aid = B.aid
FROM sub_aminer_paper A
INNER JOIN sub_aminer_author2paper B ON A.pid = B.pid

#2

I think you are describing an UPDATE scenario here, not an INSERT, if I understand you correctly.

我认为你在这里描述一个UPDATE场景，而不是INSERT，如果我理解正确的话。

Update statements can include a from part with multiple tables joined like this:

Update语句可以包含一个包含多个表的from部分，如下所示：

UPDATE sub_aminer_paper 
SET aid = sub_aminer_author2paper.aid
FROM sub_aminer_author2paper 
INNER JOIN sub_aminer_paper ON sub_aminer_paper.pid = sub_aminer_author2paper.pid

This statement will update the aid column for existing rows in sub_aminer_paper with the aid value found in sub_aminer_author2paper for matching pid.

此语句将使用sub_aminer_author2paper中的辅助值更新sub_aminer_paper中现有行的辅助列，以匹配pid。

#1