有两张边
A表里面有5个字段(C,D,E,F,G)
B表里面有5个字段(C,D,L,M,N)要更新B.L=A.E 条件是 A.C=B.C AND A.D=B.D AND A.F<B.M AND A.G>B.N这条SQL语句怎么写啊?用UPDATE
A表里面有5个字段(C,D,E,F,G)
B表里面有5个字段(C,D,L,M,N)要更新B.L=A.E 条件是 A.C=B.C AND A.D=B.D AND A.F<B.M AND A.G>B.N这条SQL语句怎么写啊?用UPDATE
调试欢乐多
set L = A.E
from A
where A.C=B.C
AND A.D=B.D
AND A.F<B.M
AND A.G>B.N
A表有1000W,B表有30W
update b set l=a.e
from a,b where a.c=b.c and a.d=b.d and a.f<b.m and a.g>b.n
update B
set L = A.E
from A,b
where A.C=B.C
AND A.D=B.D
AND A.F<B.M
AND A.G>B.N
--将A B 表中的 C D字段作为索引 会加快update定位速度 节省一点时间update B set B.L = A.E from A with(HOLDLOCK) ,B with(HOLDLOCK) where A.C=B.C AND A.D = B.D AND A.F < B.M AND A.G > B.N
set L = b.E
from B c join (select a.c,a.d,a.l,a.m,a,n from B a where exeists(select 1 from A where A.C=b.C
AND A.D=b.D
AND A.F<b.M
AND A.G>b.N
)) d on c.d=d.d and c.c=d.c and c.l=d.l and c.n=d.n and c.m=d.m 如果某一列的值是唯一的话,可以只选一个相等
set L = d.E
from B c join (select a.c,a.d,a.l,a.m,a,n,b.E from B a join A b on a.C=b.C
AND a.D=b.D
AND a.F<b.M
AND a.G>b.N
)) d on c.d=d.d and c.c=d.c and c.l=d.l and c.n=d.n and c.m=d.m
update B
set L = d.E
from B c join (select a.c,a.d,a.l,a.m,a,n,b.E from B a join A b on a.C=b.C
AND a.D=b.D
AND b.F<a.M
AND b.G>a.N
)) d on c.d=d.d and c.c=d.c and c.l=d.l and c.n=d.n and c.m=d.m这个才对