Question

What is the best approach to add a column to an existing table with values from a join...For example:

如果我加入表A,表B.

Select
A.Column1,
A.Column2,
B.Column1,
B.Column2,
B.Column3
FROM A
INNER JOIN B
ON A.Column1 = B.Column2

基本上,我只想抄录表A中表B中的现有一栏,我如何能够在表A中增加新的A.Column3,以便与B.Column3在合并的基础上相匹配?

Answer 1

为此:

alter table A add column3 datatype

update A 
set column3 = B.column3 
from A inner join B on A.Column1 = B.Column2

Answer 2

注意这可能是而不是最有效的方法

alter table A add column3 [yourdatatype];

update A set column3 = (select column3 from B where A.Column1 = B.Column2) 
  where exists (select column3 from B where A.Column1 = B.Column2)

Answer 3

首先使用<>>t table, 指令添加新栏。

序号使用update 指令将B数值列入A编的栏目。

Answer 4

You can do this:

alter A add column3 datatype;

update A
inner join (select column2 ,column3, count(*) as cnt from B group by column2) b
on A.column1 = b.column2 set 
A.column3 = b.column3;

友情链接