CODEWITHSUNDEEP
mysqlsql
MOJOJO
MOJOJO

Reputation: 83

SQL join table with same column name

I want to join two tables with the same column names and update it using by ID (unique key)

table structure

Upvotes: 4

Views: 20837

Answers (4)

Jason Clark
Jason Clark

Reputation: 1423

Try this also:

update Table1 set Table1.age=Table2.age from table1 inner join table2 on Table1.id=Table2.id

Upvotes: 0

Krishnakumar
Krishnakumar

Reputation: 745

With your Example:

select * from Table1;
+----+------+------+--------+
| ID | Name | age  | Gender |
+----+------+------+--------+
|  1 | Pars |   23 | M      |
|  2 | Pepe |   24 | M      |
|  3 | Pio  |   25 | M      |
|  4 | Pak  |   26 | F      |
+----+------+------+--------+

select * from Table2;
+------+------+------+--------+
| ID   | Name | age  | Gender |
+------+------+------+--------+
|    1 | Pars |   30 | M      |
|    2 | Pepe |   31 | M      |
|    3 | Pio  |   32 | M      |
+------+------+------+--------+

After the Update Query:

Update Table1 join Table2 using(ID) set Table1.AGE=Table2.AGE;

RESULT:

select * from Table1;
+----+------+------+--------+
| ID | Name | age  | Gender |
+----+------+------+--------+
|  1 | Pars |   30 | M      |
|  2 | Pepe |   31 | M      |
|  3 | Pio  |   32 | M      |
|  4 | Pak  |   26 | F      |
+----+------+------+--------+

Upvotes: 5

anjali
anjali

Reputation: 84

Used left join to join the tables. Try this code:- 

select a.id ,
       coalesce(a.name,b.name) as name
      ,coalesce(b.age,a.age)as age
      ,coalesce(a.gender,a.gender)as gender
from table1 as a
left join Table2 as b 
on a.id=b.id

Upvotes: 0

neevan
neevan

Reputation: 31

I think that this may help you.....

The query uses the inner join to join the table1 and table2

SELECT T1.name1,T2.name2
FROM `table1` T1 
INNER JOIN `table2` T2 ON t2.name1=t1.PrimaryKey;

Upvotes: 0

Related Questions