批量 UPDATE

[yyixin]

create table #A(
id int,
 v INT NOT NULL
 );

INSERT INTO #A
VALUES(1,2),(2,8),(3,6),(4,3);
SELECT * FROM #A ORDER BY id;

DECLARE @aTable TABLE (id int,v INT NOT NULL)
INSERT @aTable select  id,v  FROM #A
WHERE id < 3;
SELECT * FROM @aTable ORDER BY id;

drop table #A 

/*@aTable的id对应#A的id，如何根据@aTable的id,对#A的v字段加1,结果为：
id          v
----------- -----------
1           3
2           9
3           6
4           3

*/

[guguda2008]

create table #A(
id int,
 v INT NOT NULL
 );

INSERT INTO #A
VALUES(1,2),(2,8),(3,6),(4,3);
SELECT * FROM #A ORDER BY id;

DECLARE @aTable TABLE (id int,v INT NOT NULL)
INSERT @aTable select  id,v  FROM #A
WHERE id < 3;
SELECT * FROM @aTable ORDER BY id;

UPDATE #A SET V=V+1 WHERE ID IN (SELECT ID FROM @aTable)
SELECT * FROM #A
drop table #A 

/*@aTable的id对应#A的id，如何根据@aTable的id,对#A的v字段加1,结果为：
id          v
----------- -----------
1           3
2           9
3           6
4           3

*/

[-晴天]

create table #A(
id int,
 v INT NOT NULL
 );

INSERT INTO #A
VALUES(1,2),(2,8),(3,6),(4,3);
SELECT * FROM #A ORDER BY id;

DECLARE @aTable TABLE (id int,v INT NOT NULL)
INSERT @aTable select  id,v  FROM #A
WHERE id < 3;
SELECT * FROM @aTable ORDER BY id;

update #A set v=v+1 from #A t1 inner join @aTable t2 on t1.id=t2.id
--or
update #A set v=v+1 where id in(select id from @aTable) 

drop table #A 

[guguda2008]

为了防止抢分，多写几种写法

create table #A(
id int,
 v INT NOT NULL
 );

INSERT INTO #A
VALUES(1,2),(2,8),(3,6),(4,3);
SELECT * FROM #A ORDER BY id;

DECLARE @aTable TABLE (id int,v INT NOT NULL)
INSERT @aTable select  id,v  FROM #A
WHERE id < 3;
SELECT * FROM @aTable ORDER BY id;

UPDATE #A SET V=V+1 
WHERE ID IN (
SELECT ID FROM @aTable
)

UPDATE #A SET V=V+1 
FROM #A T1 
WHERE EXISTS(
SELECT 1 FROM @ATABLE T2 WHERE T2.ID=T1.ID
)

UPDATE T1 SET V=T1.V+1 
FROM #A T1 
INNER JOIN @ATABLE T2 ON T1.ID=T2.ID

UPDATE T1 SET V=T1.V+1
FROM #A T1,@ATABLE T2 WHERE T1.ID=T2.ID

SELECT * FROM #A
drop table #A 

/*
1	6
2	12
3	6
4	3
*/

[yyixin]

谢谢“guguda2008”！一段时间没接触了，基本的都给忘了