http://blogs.msdn.com/sqlpfe/archive/2010/01/06/tsql-coding-patterns-i.aspx
So instead of the usual:
UPDATE dbo.Foo
SET Column = 'Value'
Try:
DECLARE @UpdatedRows(PK_Id int NOT NULL PRIMARY KEY)
DECLARE @var INT
SELECT @var=0 -- this resets @@ROWCOUNT=1
WHILE @@ROWCOUNT >0
BEGIN
UPDATE TOP(1500) BAR
SET Column='Value'
OUTPUT inserted.PK_ID
INTO @UpdatedRows
FROM dbo.BAR as BAR
WHERE NOT EXISTS (SELECT 1 FROM @UpdatedRows UPD WHERE UPD.PK_ID=BAR.PK_ID)
END
I prefer one of the ways the commentator offered: (easier to follow)
WHILE @@ROWCOUNT >0
BEGIN
UPDATE TOP(1500) BAR
SET Column='Value'
WHERE
Column <> 'Value'
END
No comments:
Post a Comment