mysql编码映射表_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