SQL UPDATE TOP () or UPDATE with SELECT TOP

Question

I have couple thousands records and I need to update them in batches of 350 records.

I would like to know if there is any difference in the below two update statements and if one of them would work 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')

yes top 150 records are chosen randomly. Records updated in both the queries might not be same — Pரதீப், Feb 01 '17 at 10:46
a couple of thousand records? does that really need to be done in batches? — Tanner, Feb 01 '17 at 11:12
Does this answer your question? [how can I Update top 100 records in sql server](https://stackoverflow.com/questions/1198364/how-can-i-update-top-100-records-in-sql-server) — Michael Freidgeim, Jun 08 '22 at 11:05

Pரதீப் · Accepted Answer · 2018-05-23T04:00:37.297

First statement will be faster. But the top 150 records are chosen randomly. Records updated in both the queries might not be same. Since you are spitting the updates into batches your approach may not update all records.

I will do this using following consistent approach 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 = ''

Also you don't have to worry transaction log size when updating couple thousands records there is no need of batches here

score 0 · Answer 2 · answered Feb 01 '17 at 11:36

Filter data from database1 and then join by id. Below methods is more realistic for selecting and update data.

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

SQL UPDATE TOP () or UPDATE with SELECT TOP

Statement 1:

Statement 2:

2 Answers2