目前有两张表:
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