SQL将分类表的主键,更新到分类详情表的外键上去

目前有两张表:

FLowKind表:物质名录种类表.字段如下

Id, KindEngName, Casnumber, Classifications

Flow表:物质名录种类下的详情表.字段如下:

Id,FLOW_eng_name,FLOW_casnumber,FLOW_class0_classCode,KindId

关系:FLowKind --------1:N-------->Flow

需求:FLowKind和Flow是按照,字段KindEngName, Casnumber, Classifications和FLOW_eng_name,FLOW_casnumber,FLOW_class0_classCode关联.

这样子关联,查询不太方便,并且查询效率也特别低.所以在FLowKind 表上加一个外键KindId,需要将FlowKind的Id,按照3个关联字段同步到Flow表的KindId上去.

1.先将Flow和FLowKind按照

on a.FLOW_eng_name = b.KindEngName and 
a.FLOW_casnumber = b.Casnumber and
a.FLOW_class0_classCode = b.Classifications 

这三个字段关联.

select a.FLOW_eng_name, a.FLOW_casnumber, a.FLOW_class0_classCode,a.KindId,
b.Id, b.KindEngName,b.Casnumber,b.Classifications
from Flow a
left join FlowKind b 
on a.FLOW_eng_name = b.KindEngName and 
a.FLOW_casnumber = b.Casnumber and
a.FLOW_class0_classCode = b.Classifications 

2.在用with temp as 更新Id到KindId.

with temp as(
select a.FLOW_eng_name, a.FLOW_casnumber, a.FLOW_class0_classCode,a.KindId,
b.Id, b.KindEngName,b.Casnumber,b.Classifications
from Flow a
left join FlowKind b 
on a.FLOW_eng_name = b.KindEngName and 
a.FLOW_casnumber = b.Casnumber and
a.FLOW_class0_classCode = b.Classifications)
update temp set temp.KindId = temp.Id