首先在两个表中都添加一个自增标识的ID列,(确保两个id中都有对应的值,即1,2,3...)
然后执行:update tbla set tbla_b=b.tblb_b from
tbla A left outer join tblb b on a.id=b.id and left(a.tbla_a,2)=left(tblb_a,2) and a.id=b.id
--测试结果:
select tbla_a ,tbla_b from tblaaaa xxx
aba NULL
abc xxb2
(所影响的行数为 3 行)
然后执行:update tbla set tbla_b=b.tblb_b from
tbla A left outer join tblb b on a.id=b.id and left(a.tbla_a,2)=left(tblb_a,2) and a.id=b.id
--测试结果:
select tbla_a ,tbla_b from tblaaaa xxx
aba NULL
abc xxb2
(所影响的行数为 3 行)
然后执行:update tbla
set tbla_b=b.tblb_b
from tbla A
inner join tblb b
on a.id=b.id
and left(a.tbla_a,2)=left(tblb_a,2)
and a.id=b.id
update tbla
set tbla_b=b.tblb_b
from tbla a
inner join tblb b
on a.id=b.id
and left(ltrim(a.tbla_a),2)=left(ltrim(b.tblb_a),2)
select a.tbla_a,isnull(a.tbla_b,b.tblb_b) as tbla_b
from tbla a
inner join tblb b
on a.id=b.id
where left(ltrim(a.tbla_a),2)=left(ltrim(b.tblb_a),2)