我现有两个表A,BA 表的结构
ID, Column1,column2,column3,....., versionB表的结构
ID, Column4,column5,column6,...,XVersion注释:
A.Version int
B.Xversion int
判断B 表中与A表相同的条件是: A.columN1=B.column4 and A.Column2=B.Column5 and A.Column3=B.Column6我想将B表中与A表相同的记录(但Version不同 eg: A.Version != B.Xversion), 在A表中更新(只更新Version)
,将B.Xversion 更新到A表中对应的 version
更新version 还有一个原则就是: B.Xversion>A.version 更新, 否则不更新用 update set ... select ... 如何写sql
ID, Column1,column2,column3,....., versionB表的结构
ID, Column4,column5,column6,...,XVersion注释:
A.Version int
B.Xversion int
判断B 表中与A表相同的条件是: A.columN1=B.column4 and A.Column2=B.Column5 and A.Column3=B.Column6我想将B表中与A表相同的记录(但Version不同 eg: A.Version != B.Xversion), 在A表中更新(只更新Version)
,将B.Xversion 更新到A表中对应的 version
更新version 还有一个原则就是: B.Xversion>A.version 更新, 否则不更新用 update set ... select ... 如何写sql
set A.version=B.xversion
from A join B on A.columN1=B.column4 and A.Column2=B.Column5 and A.Column3=B.Column6
where B.xversion>A.version
set a.version=b.xversion from a left join b on
A.columN1=B.column4 and A.Column2=B.Column5 and A.Column3=B.Column6
where B.xversion>A.version
离题了, 应该是 be A.version=B.XVersionto: dobear_0922 & mugua604
如果 B 表中找到不只条记录满足条件,咋办
我这样写的,但也不知道有没有问题:
PDATE a SET Version=(
SELECT DISTINCT MAX([XVersion])
FROM b
WHERE
B.columN4=A.column1 and
B.Column5=A.Column2 and
B.Column6=A.Column3 and
B.Xversion>a.Version
)
WHERE Version in (
SELECT DISTINCT MAX([XVersion])
FROM b
WHERE
B.columN4=A.column1 and
B.Column5=A.Column2 and
B.Column6=A.Column3 and
B.Xversion>a.Version)
如果 B 表中找到不只一条记录满足条件,咋办
FROM tA a
LEFT JOION tb b
ON a.c1=b.c4 AND a.c2=b.c5 AND a.c3=b.c6 AND xversion>version