关于SQL批量修改数据

时间：2023-02-11 07:26:36
数据类似这样的：

a    b
15421    1
642    2
255    1
8754653    2
2354    1
87653    1
12536    1
9876    3


现在想将表A中2354、12536的表B数据全部修改为2。

而由于数据有上千万条，而需要修改的也有几百万，使用 UPDATE a SET b=2 WHERE a='2354' 进行修改的效率实在是太低了。有没有什么方法可以批量处理呢？对SQL比较小白，请帮忙写个事例。

20 个解决方案

#1

update tb set b=2 where a in(2354,12536)

#2

直接修改默认值好像效率也高不到哪去
关注下吧

#3

引用楼主 xgyyi 的回复:
数据类似这样的：

a    b
15421    1
642    2
255    1
8754653    2
2354    1
87653    1
12536    1
9876    3


现在想将表A中2354、12536的表B数据全部修改为2。

而由于数据有上千万条，而需要修改的也有几百万，使用 UPDATE a SET b=2 WHERE a=……


UPDATE a SET b=2 WHERE a='2354'--在A列建上索引試試

#4

现在是这样  使用我原先的方法，每次只能进行10000左右的修改，再多，就报内存不足了。用2楼的方法不知道会不会遇到这个问题，另外效率，正在实验。

另外3楼说的索引是什么哦？我试了下，需要什么唯一列。而表的原格式，是不能修改的。

#5

在A上建立以个聚集索引试试

#6

UPDATE a SET b=2 WHERE a='2354'  家索引，如果连这个效率都不高的话，那估计就木有办法了

#7



with cte as

(

     select *　from tb where a=2354 or a=12536

)

update cte set b=2

#8

引用 7 楼 public0011 的回复:
SQL code

with cte as
(
     select *　from tb where a=2354 or a=12536
)
update cte set b=2


可以用临时表 效率应该比CTE高

#9

二楼：update tb set b=2 where a in(2354,12536,.....)
由于每次修改是10000左右的数据，经过15分钟，仍然没执行完，此方法对与此问题效率很低。


用我原始的方法，修改8300条数据，用时3:58。  共需要修改80*2*7000=112W的数据，用时得9个小时左右。


7楼的方法正在测试中

#10

你是什么破机器啊,update b=2, 8300条数据要近4分钟!
或许,你的机器有什么问题吧,内存运用得如何了?CPU工作了多少?
再,重启一下数据库看.

#11

set rowcount 1000;


update tb set b=2 where a in(2354,12536)


while @@rowcount>0

update tb set b=2 where a in(2354,12536)

set rowcount 0


用個熱循環刪除試試

#12

此表中，共有数据800万数据。
内存已用：1910MB（其中sqlservr.exe占用1491MB）
内存剩余：120MB

CPU在执行命令时为100%，不执行时0%。

#13

 where a in(2354,12536)


括号内数据量达到1万时，其占用的时间将是恐怖的

#14

///...这内存太小了，开AWE？
引用 12 楼 xgyyi 的回复:
此表中，共有数据800万数据。
内存已用：1910MB（其中sqlservr.exe占用1491MB）
内存剩余：120MB

CPU在执行命令时为100%，不执行时0%。

#15

这个只是工业生产线上的一个机器   其配置根本与专业的没法比。

#16

在 a 列上创建非聚集索引试试. 



BEGIN TRANSACTION

CREATE NONCLUSTERED INDEX IX_tb dbo.tb

a) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

ALTER TABLE dbo.tbET (LOCK_ESCALATION = TABLE)

GO

COMMIT

#17


BEGIN TRANSACTION

CREATE NONCLUSTERED INDEX IX_tb dbo.tb

(a) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

ALTER TABLE dbo.tbET (LOCK_ESCALATION = TABLE)

GO

COMMIT

#18

有没有办法，把没修改过的数据单独弄到个虚拟的表中，然后再针对其修改呢？这样效率应该会越来越快吧

#19

16楼的  应该怎么使用呢？

后面接命令吗？

#20

算了，最终还是采用了最笨的方法。

#21

#1

update tb set b=2 where a in(2354,12536)

#2

直接修改默认值好像效率也高不到哪去
关注下吧

#3

引用楼主 xgyyi 的回复:
数据类似这样的：

a    b
15421    1
642    2
255    1
8754653    2
2354    1
87653    1
12536    1
9876    3


现在想将表A中2354、12536的表B数据全部修改为2。

而由于数据有上千万条，而需要修改的也有几百万，使用 UPDATE a SET b=2 WHERE a=……


UPDATE a SET b=2 WHERE a='2354'--在A列建上索引試試

#4

现在是这样  使用我原先的方法，每次只能进行10000左右的修改，再多，就报内存不足了。用2楼的方法不知道会不会遇到这个问题，另外效率，正在实验。

另外3楼说的索引是什么哦？我试了下，需要什么唯一列。而表的原格式，是不能修改的。

#5

在A上建立以个聚集索引试试

#6

UPDATE a SET b=2 WHERE a='2354'  家索引，如果连这个效率都不高的话，那估计就木有办法了

#7



with cte as

(

     select *　from tb where a=2354 or a=12536

)

update cte set b=2

#8

引用 7 楼 public0011 的回复:
SQL code

with cte as
(
     select *　from tb where a=2354 or a=12536
)
update cte set b=2


可以用临时表 效率应该比CTE高

#9

二楼：update tb set b=2 where a in(2354,12536,.....)
由于每次修改是10000左右的数据，经过15分钟，仍然没执行完，此方法对与此问题效率很低。


用我原始的方法，修改8300条数据，用时3:58。  共需要修改80*2*7000=112W的数据，用时得9个小时左右。


7楼的方法正在测试中

#10

你是什么破机器啊,update b=2, 8300条数据要近4分钟!
或许,你的机器有什么问题吧,内存运用得如何了?CPU工作了多少?
再,重启一下数据库看.

#11

set rowcount 1000;


update tb set b=2 where a in(2354,12536)


while @@rowcount>0

update tb set b=2 where a in(2354,12536)

set rowcount 0


用個熱循環刪除試試

#12

此表中，共有数据800万数据。
内存已用：1910MB（其中sqlservr.exe占用1491MB）
内存剩余：120MB

CPU在执行命令时为100%，不执行时0%。

#13

 where a in(2354,12536)


括号内数据量达到1万时，其占用的时间将是恐怖的

#14

///...这内存太小了，开AWE？
引用 12 楼 xgyyi 的回复:
此表中，共有数据800万数据。
内存已用：1910MB（其中sqlservr.exe占用1491MB）
内存剩余：120MB

CPU在执行命令时为100%，不执行时0%。

#15

这个只是工业生产线上的一个机器   其配置根本与专业的没法比。

#16

在 a 列上创建非聚集索引试试. 



BEGIN TRANSACTION

CREATE NONCLUSTERED INDEX IX_tb dbo.tb

a) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

ALTER TABLE dbo.tbET (LOCK_ESCALATION = TABLE)

GO

COMMIT

#17


BEGIN TRANSACTION

CREATE NONCLUSTERED INDEX IX_tb dbo.tb

(a) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

ALTER TABLE dbo.tbET (LOCK_ESCALATION = TABLE)

GO

COMMIT

#18

有没有办法，把没修改过的数据单独弄到个虚拟的表中，然后再针对其修改呢？这样效率应该会越来越快吧

#19

16楼的  应该怎么使用呢？

后面接命令吗？

#20

算了，最终还是采用了最笨的方法。

#21

标签：

相关文章