SQL UPDATE TOP () or UPDATE with SELECT TOP

Question

I have several thousand records, and I need to update them in batches of 350 records.

I would like to know if there is any difference in the following two update operations, and if one of them works faster, use less database resources, etc.

Statement 1:

UPDATE TOP (350) database1
SET value1 = '', value2 ='', value3 = ''
WHERE value1 = '123'

Statement 2:

    UPDATE database1
    SET value1 = '', value2 ='', value3 = ''
    WHERE ID in 
       (SELECT TOP 350 ID FROM database1
       WHERE value1 = '123')

Answer 1

The first operator will be faster. But the first 150 entries are randomly selected. Entries updated in both queries may not be the same. Since you spit out updates in a batch, your approach may not update all records.

I will do this using the following approach, more consistent than your approach.

;WITH cte
     AS (SELECT TOP (350) value1,
                          value2,
                          value3
         FROM   database1
         WHERE  value1 = '123'
         ORDER  BY ID -- or any other column to order the result 
        )
UPDATE cte
SET    value1 = '',
       value2 = '',
       value3 = '' 

, .

Answer 2

1, id. .

UPDATE database1 
    SET value1 = '', value2 ='', value3 = ''
    FROM (
        SELECT top 350 ID
        FROM database1
        WHERE value1 = '123'
        ) as db1
    WHERE db1.ID = database1.ID