表更新问题
现有表T1 , 有字段:AT,A,BT,B,ID
表T2, 有字段:DT,D,ID
拿T2的数据更新T1:如果DT=1 侧:AT=AT+DT,A=A+D
如果DT=2 侧:BT=BT+DT,B=B+D
更新条件为:ID=ID
我是这样写的:update T1 set AT=case when D=1 then AT+1 else AT end,
A=case when D=1 then A+D else A end ,
BT=case when D=2 then BT+1 else AT end,
B=case when D=2 then B+D else A end
from T1 left outer join on T2 T1.ID=T2.ID
可是更新后的结果为:要么只更新到AT\A,要么只更新到BT\B,是什么原因呢?
[解决办法]
你的需求本來就是，如果DT = 1，那麼就更新AT和A，如果DT=2，就更新BT和B啊。
所以“要么只更新到AT\A,要么只更新到BT\B”，這沒有什麼不對啊。
[解决办法]
declare @t1 table(AT int,A int,BT int,B int,ID int)
insert @t1 select 0, 0, 0, 0, 1
declare @t2 table(DT int,D int,ID int)
insert @t2
select 1, 2, 1 union all
select 2, 3, 1
----更新
UPDATE a SET
AT = AT + (select DT from @t2 where ID = a.ID and DT = 1 ),
A = A + (select D from @t2 where ID = a.ID and DT = 1),
BT = BT + (select DT from @t2 where ID = a.ID and DT = 2),
B = B + (select D from @t2 where ID = a.ID and DT = 2)
FROM @t1 AS a
----查看
select * from @t1
/*结果
AT A BT B ID
----------- ----------- ----------- ----------- -----------
1 2 2 3 1
*/
