Mysql 将一表的一字段数据填到对应另一表字段中

任务为：

将tests_copy1中hm字段填上相应的数据，该数据来源于spacegroups中的hm数据。

要求tests_copy1中的spacegroups_id与spacegroups中的number值对应。

 

 

 

 

一下两种方法：

1、

INSERT into 
tests_copy1_copy2
(chem_formula,natoms,nsites,
ntypes,x1,x2,x3,y1,y2,y3,z1,
z2,z3,sxx,syy,szz,sxy,syz,
szx,spacegroup_id,volume_pa,
energy_pa,
magmom_pa,label,
entry_id,id1,id2,

hm) 

SELECT 

tests_copy1.chem_formula,
tests_copy1.natoms,
tests_copy1.nsites,
tests_copy1.ntypes,
tests_copy1.x1,
tests_copy1.x2,tests_copy1.x3,
tests_copy1.y1,tests_copy1.y2,
tests_copy1.y3,tests_copy1.z1,
tests_copy1.z2,tests_copy1.z3,
tests_copy1.sxx,tests_copy1.syy,
tests_copy1.szz,tests_copy1.sxy,
tests_copy1.syz,
tests_copy1.szx,tests_copy1.spacegroup_id,
tests_copy1.volume_pa,tests_copy1.
energy_pa,
tests_copy1.magmom_pa,
tests_copy1.label,tests_copy1.entry_id,
tests_copy1.id1,
tests_copy1.id2,

spacegroups.hm
 from 
tests_copy1 
left join  
spacegroups on tests_copy1.spacegroup_id = spacegroups.number

 

方法二、

update 

tests_copy1_copy2 ,spacegroups 
set 

tests_copy1_copy2.hm =spacegroups.hm
where 

tests_copy1_copy2.spacegroup_id=spacegroups.number