SQL Server : when using multiple UPDATES in a single query, will the WHERE clause use the current or updated field values?

Question

This is a very simple question, but the SQL Server documentation doesn't explain this as clearly as I would like, so I would appreciate someone spelling it out for me.

I am updating a table with new values based on a spreadsheet - which means I'm using multiple UPDATE statements in a single query:

UPDATE Asset SET AssetID = 'NewID' WHERE AssetID = 'OldID'

That's the basic query, but with actual ID numbers in place of NewID and OldID, so the whole query looks a lot like this;

UPDATE Asset SET AssetID = '001' WHERE AssetID = '111'
UPDATE Asset SET AssetID = '002' WHERE AssetID = '112'
UPDATE Asset SET AssetID = '003' WHERE AssetID = '113'

The problem I'm concerned about running into, is that there may be some instances where, depending on the order in which SQL updates these fields, and what value it uses in the WHERE clause, I may end up inadvertently updating multiple rows.

For example:

UPDATE Asset SET AssetID = '001' WHERE AssetID = '111'
UPDATE Asset SET AssetID = '002' WHERE AssetID = '001'
UPDATE Asset SET AssetID = '003' WHERE AssetID = '002'

Now imagine, that prior to me running the above query, I have the following table:

AssetID
111
001
002

What I want to happen, is for the WHERE clause in my UPDATE statements, to use only the AssetID that the column CURRENTLY has - i.e. the ID in use prior to running the query.

This would give me the table below:

AssetID
001
002
003

What I'm worried will ACTUALLY happen, is that it'll process the updates in sequence, and use the updated ID in the WHERE clause, which means my table will instead look like this:

AssetID
003
003
003

Now, during my research on this I managed to find some documentation on how SQL Server handles this - which is that it uses the UPDATED value. It states that this behaviour is different to how "Normal SQL" handles it, so I'm hoping this will not be a problem when using SQL Server.

Can someone confirm?

Answer 1

This is a manifestation of the Halloween problem.
You could also get constraint errors because of how updates are applied (especially on uniqueness)

The way to do many updates in one go to avoid intermediate errors is to do a single update

UPDATE
    A
SET
    A.AssetID = N.NewAssetID
FROM
   Asset A
   JOIN
   SomeTempTableOrTableVar N ON A.AssetID = OldAssetId 

SomeTempTableOrTableVar is a tablevalued parameter, temp table or table variable of values clause with 2 columns

Edit: Or use an extra column as per OP's comment.

Example

UPDATE Asset SET AssetID = '001' WHERE AssetID = '111'
UPDATE Asset SET AssetID = '002' WHERE AssetID = '001'
UPDATE Asset SET AssetID = '003' WHERE AssetID = '002'

...becomes this:

UPDATE
    A
SET
    A.AssetID = N.NewAssetID
FROM
   Asset A
   JOIN
   (VALUES
      ('111', '001'),
      ('001', '002'),
      ('002', '003')
   ) N (OldAssetId, NewAssetID) ON A.AssetID = OldAssetId