MS SQL Insert into select join

问题:

I have two table

    TABLE1                        TABLE2
COLUMN1 COLUMN2               COLUMN1 COLUMN2
John      56                     56      A
Bob       45                     45      B 
Eva       68                     68      C

And I need add column to one table with fact I will insert into new column value from other table using function join

        TABLE1
COLUMN1 COLUMN2 COLUMN3
John      56      A
Bob       45      B 
Eva       68      C

ALTER TABLE [dbo].[Table1]
ADD Column3 nvarchar(255); 

INSERT INTO [dbo].[Table1] (column3)
SELECT table2.column2
from [dbo].[Table2] left join [dbo].[table1]
on table1.column2 = table2.column1

But I am getting

        TABLE1
COLUMN1 COLUMN2 COLUMN3
John     56       A
Bob      45       B 
Eva      68       C
NULL     NULL     A
NULL     NULL     B
NULL     NULL     C

Can you help me to fix my insert?

回答1:

Really you need to UPDATE the table setting column3

UPDATE t1 
SET column3 = t2.column2
FROM [dbo].[Table1] t1
JOIN [dbo].[table2] t2 ON t1.column2 = t2.column1

回答2:

You have to use INNER JOIN:

INNER JOIN Table1 ON Table1.Column2 = Table1.Column1

回答3:

Try writing inner join instead of left join.

回答4:

In the INSERT INTO change the JOIN from LEFT to INNER, and join on table1.column1 instead of table1.column2

INSERT INTO [dbo].[Table1] (column3)
SELECT table2.column2
from [dbo].[Table2] 
INNER JOIN [dbo].[table1]
on table1.column1 = table2.column1

回答5:

What you are trying to do is UPDATE table. For simplicity: columns in first table are numed column1, etc. and in the second col1, etc. Then you need just simple update statement.

UPDATE Table1 SET column3 = col2
              WHERE column2 = col1