求一批量更新数据的语句
有a,b两表如下:
a: b:
aID aLocation aResult bID bLocation bLocked
1 1 L1 N
2 2 L2 N
3 3 L3 Y
4
5
6
更新的时候首先判断 bID 中是否含有与a相同的aID,有的话aLocation写为对应b表的bLocation,判断bLocked 是否是N,是的话将a表的aResult写为X,负责写为Y,如果b表中没有与aID相同的bID,aLocation不变,aResult写为Z.结果如下:
a:
aID aLocation aResult
1 L1 X
2 L2 X
3 L3 Y
4 Z
5 Z
6 Z
谢谢!!
[解决办法]
Update
A
Set
aLocation = isnull(B.bLocation, ' '),
aResult = (Case B.bLocked When 'N ' Then 'X ' When 'Y ' Then 'Y ' Else 'Z ' End)
From
A
Left Join
B
On A.aID = B.bID
[解决办法]
--上面的有點問題
Update
A
Set
aLocation = IsNull(B.bLocation, A.aLocation),
aResult = (Case B.bLocked When 'N ' Then 'X ' When 'Y ' Then 'Y ' Else 'Z ' End)
From
A
Left Join
B
On A.aID = B.bID
--或者
Update
A
Set
aLocation = IsNull(B.bLocation, ' '),
aResult = (Case B.bLocked When 'N ' Then 'X ' When 'Y ' Then 'Y ' Else 'Z ' End)
From
A
Left Join
B
On A.aID = B.bID
