更新数据的脚本

转载于 2018-03-08 11:04:00 发布 · 91 阅读

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/sxjljj/p/8527189.html

本文介绍了一种使用T-SQL在SQL Server中进行高效批量更新记录的方法。通过使用ROWLOCK提示和TOP(@Rows)子句结合WHILE循环，可以有效地避免锁竞争并减少内存使用。此外，还展示了如何通过子查询和EXISTS谓词来实现条件更新。

--UPDATE dbo.Notice WITH(ROWLOCK) SET IsNoAOG=0,IsSection35=0 WHERE IsNoAOG IS NULL AND IsSection35 IS NULL

DECLARE @Rows INT = 50000

WHILE 1=1 BEGIN UPDATE TOP (@Rows) dbo.Notice WITH (ROWLOCK) SET IsNoAOG=0,IsSection35=0 FROM dbo.Notice n WHERE IsNoAOG IS NULL AND IsSection35 IS NULL IF @@RowCount < @Rows BREAK END

--==================================

DECLARE @Rows INT = 50000

WHILE 1=1 BEGIN ;WITH Part1 AS ( SELECT DISTINCT NotIntNo FROM dbo.Charge c WITH (NOLOCK) WHERE ChgNoAOG = 'Y' AND ChargeStatus < 900 ) UPDATE TOP (@Rows) dbo.Notice WITH (ROWLOCK) SET IsNoAOG = 1 FROM dbo.Notice n WHERE ISNULL(IsNoAOG, 0) = 0 AND NoticeStatus < 900 AND EXISTS ( SELECT 1 FROM Part1 p WITH (NOLOCK) WHERE p.NotIntNo = n.NotIntNo ) IF @@RowCount < @Rows BREAK END

转载于:https://www.cnblogs.com/sxjljj/p/8527189.html