Insert into one table base on join result from 2 o

问题:

I have 3 tables in MySQL: def_table,spot_table,tag_mapping_spot_table.

def_table

tag_id       tag_ja
2010490043   アラバマ・アンド・ガルフ・コースト鉄道
2010680003   american_football、サッカー、スポーツ
2010970036   ノーフォークマツの種
.........    ..........

spot_table

spot_id   spot_name
1          NULL  
2          NULL
3          NULL
...        ....

tag_mapping_spot_table

spot_id   tag_id
1         2010490043 
2         2010680003
3         2010970036
....      .....

All I want to do is join "tag_mapping_spot_table" and "def_table" base on column tag_id and then join the result with "spot_table" base on spot_id.I want to put the result from column tag_ja to column spot_name

Some thing like

insert into spot(spot_name) where spot_id = b.spot_id
(select a.tag_ja,b.spot_id from def_table a join tag_mapping_spot b 
on a.tag_id = b.tag_id 

Here is the result I want in spot_table

spot_id   spot_name
1         アラバマ・アンド・ガルフ・コースト鉄道 
2         american_football、サッカー、スポーツ
3         ノーフォークマツの種

回答1:

You need a UPDATE (not INSERT): UPDATE DOCS

 UPDATE spot_table ST 
 INNER JOIN tag_mapping_spot_table c ON c.spot_id = ST.spot_id 
 INNER JOIN def_table b ON b.tag_id = c.tag_id 
 SET ST.spot_name = b.tag_ja