MySQL: fixing data using a different table

Question

I need to commit data in the "tag" table using the "tag2" table,

matching "tag.id" and "tag2.id" and if they match, replace "tag2.name" with "tag.name" in the "tag" table,

table structures:

tag:

id     name
1     test
2     test
3     test
4     Tom hancks
5     test
6     amazon
7     car
8     BMW
9     search

tag2:

id     name
1     Google
2     yahoo
3     Microsoft
4     Tom hancks
5     facebook

to return the "tag" table like this:

tag:

id     name
1     Google
2     yahoo
3     Microsoft
4     Tom hancks
5     facebook
6     amazon
7     car
8     BMW
9     search

+3

mwafi Apr 24 '15 at 5:39

3 answers

Try the following:

update tag t1
inner join tag2 on t1.id= t2.id set t1.name=t2.name

+2

Sashi kant Apr 24 15 at 5:43

try it

update tag t, tag2 t2 
set t.name=t2.name
where t.id=t2.id

+1

Prabin meitei Apr 24 15 at 5:42 am

Danyal sandeelo · Accepted Answer · 2015-04-24T05:42:12+0000

You can do this using an inner join.

 update tag  inner join tag2
 on tag.id = tag2.id     
 set tag.name = tag2.name