表A中有如下数据id total_num 1_num 2_num
11 100 50 50
21 200 150 50想UPDATE成如下数据
id total_num 1_num 2_num
11 300 200 100
即:把ID=21的total_num 1_num 2_num的数据分别加到
ID=11对应的列上
如何写SQL语句
谢谢!
11 100 50 50
21 200 150 50想UPDATE成如下数据
id total_num 1_num 2_num
11 300 200 100
即:把ID=21的total_num 1_num 2_num的数据分别加到
ID=11对应的列上
如何写SQL语句
谢谢!
set total_num=x.total_num+y.total_num,
[1_num]=x.[1_num]+y.[1_num],
[2_num]=x.[2_num]+y.[2_num]
from a x,a y
where x.id=11
and y.id=21delete a
where id=21
INSERT @T SELECT 11,100,50,50
UNION ALL SELECT 21,200,150,50
--
UPDATE A
SET TOTAL_NUM=(SELECT SUM(TOTAL_NUM) FROM @T ),
[1_NUM]=(SELECT SUM([1_NUM]) FROM @T ),
[2_NUM]=(SELECT SUM([2_NUM]) FROM @T )
FROM @T A
WHERE ID=11 OR ID=22DELETE FROM @T WHERE ID=21
--RESULT
SELECT * FROM @T