RowNum For T-SQL (Not TOP #)

Question

Trying to run an update, but I want to step through it as it's quite a bit of data and don't want to blast it all at once.

In oracle it's a bit easier to select the rows I want because you can include it in the WHERE clause, but T-SQL you include it in the select. I'm wanting to write it something along the lines of this:

Update Instances
set thing = 'new thing'
Where rownum<500

Basically want to do 500 at a time, is this syntax possible in TSQL?

Answer 1

You could use SET ROWCOUNT xxx to limit the number of rows affected by the update statement.

Something like this:

SET ROWCOUNT 500;

WHILE EXISTS (SELECT 1 FROM Instances WHERE thing = 'new thing')
BEGIN
    BEGIN TRANSACTION;
    UPDATE Instances
    SET thing = 'new thing'
    WHERE thing <> 'new thing';
    COMMIT TRANSACTION;
    CHECKPOINT;
    WAITFOR DELAY '00:00:01'; --wait one second between loops
END

If your database is configured for simple recovery model, this will not require a huge amount of log space. However, if your database is in full recovery model, you'll probably want to take log backups periodically while this is taking place.

Microsoft Docs for SET ROWCOUNT advise that it's a deprecated setting:

Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in a future release of SQL Server. Avoid using SET ROWCOUNT with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. For a similar behavior, use the TOP syntax.

Having said that, It's not on the list of removed features at this point.

Answer 2

Have a look at MS Docs about it:

Limitations and Restrictions

When TOP is used with INSERT, UPDATE, MERGE, or DELETE, the referenced rows are not arranged in any order and the ORDER BY clause can not be directly specified in these statements. If you need to use TOP to insert, delete, or modify rows in a meaningful chronological order, you must use TOP together with an ORDER BY clause that is specified in a subselect statement. See the Examples section that follows in this topic.

UPDATE TOP(500) instances
SET thing = 'new thing'
WHERE Something = 'SomethingElse';

Or

UPDATE instances
SET thing = 'new thing'
FROM (SELECT TOP (500) Id
      FROM instances
      ORDER BY Something) AS t1
WHERE instances.Id = t1.Id;

Or you can update the derived table directly without a join:

UPDATE t1
SET thing = 'new thing'
FROM (SELECT TOP (500) *
      FROM instances
      ORDER BY Something) AS t1;

An example of this approach can be found at dbfiddle.

Answer 3

You could use a common table expression with ROW_NUMBER, ORDER BY (SELECT NULL) indicates you don't care about the actual order.

WITH RowNumberCte AS
(
    SELECT Thing, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RowNumber
    FROM Instances
)

UPDATE RowNumberCte
SET Thing = 'New Thing'
WHERE RowNumber < 500

Answer 4

Looks like this has already been answered now that I take another look.

How can I Update top 100 records in SQL Server?

Update Top (500) Instances
set thing = 'new thing'
where thing <> 'new thing' ;

With the key piece that I missed was the 500 needs parenthesis.