不用游标，批量将#B中的coin列的值UPDATE到#A表的coin中（注：保留重复值）

不用游标，批量将#B中的coin列的值UPDATE到#A表的coin中（注：保留重复值）

表信息

CREATE TABLE #A(id INT,	coin INT)



INSERT INTO #A VALUES(1,10)

INSERT INTO #A VALUES(2,10)

INSERT INTO #A VALUES(3,10)

INSERT INTO #A VALUES(4,10)

INSERT INTO #A VALUES(5,10)

INSERT INTO #A VALUES(6,10)

INSERT INTO #A VALUES(7,10)



CREATE TABLE #B(id INT,	coin INT)



INSERT INTO #B VALUES(1,2)

INSERT INTO #B VALUES(2,2)

INSERT INTO #B VALUES(3,2)

INSERT INTO #B VALUES(3,2)

INSERT INTO #B VALUES(1,2)

INSERT INTO #B VALUES(1,2)

 

执行后#A中的结果

id	coin

1	16

2	12

3	14

4	10

5	10

6	10

7	10

 

SQL执行语句

UPDATE #A 

SET coin = coin + ISNULL((SELECT SUM(coin) 

FROM #B 

WHERE id = a.id),0) 

FROM #A AS a