Insert column from one table to other using Join

Question

I need to insert 3 columns from one table to another, using JOIN by 3 fields: name, surname and age

enter image description here

I need update column status, status1 and status2 in table_2 with values from table_1

IF 

table_1.name = table_2.name
table_1.surname = table_2.surname
table_1.age= table_2.age

Siddique Mahsud · Accepted Answer · 2014-03-04T03:53:44.613

1

UPDATE t2
SET 
 t2.[status]=t1.[status]
,t2.[status1]=t1.[status1]
,t2.[status2]=t1.[status2]
FROM [table_1] t1
INNER JOIN [table_2] t2 
ON (t1.name=t2.name AND t1.surname=t2.surname AND t1.age=t2.age)

As you mentioned in comments that these table from different databases, then please change only the two line like.

 FROM [yourDataBase1Name].[dbo].[table_1] t1
 INNER JOIN [yourDataBase2Name].[dbo].[table_2] t2

edited Mar 04 '14 at 03:53

answered Mar 03 '14 at 16:15

Siddique Mahsud

1,453
11
21

It does not work :( this 2 tables are from different databases. Maybe that is the reason. Because I have error message The multi-part identifier "database.table_2.staus" could not be bound – lada_slavka Mar 03 '14 at 16:25
exactly not worked if they from diff dbs. i will edit my answer – Siddique Mahsud Mar 03 '14 at 16:51

score 0 · Answer 2 · edited May 23 '17 at 10:26

0

Just update the table with Join. it will be something like this:

UPDATE t2
SET t2.status = t1.status,
    t2.status1 = t1.status1,
    t2.status2 = t1.status2
FROM t2 JOIN t1 on (t1.first_name = t2.first_name AND t1.last_name = t2.last_name AND t1.age = t2.age);

Look here for more information:SQL update query using joins

SQL Fiddle: http://sqlfiddle.com/#!3/b3951/1

edited May 23 '17 at 10:26

Community

1
1

answered Mar 03 '14 at 16:20

asafm

911
6
17

Insert column from one table to other using Join

2 Answers2