title: SQL Server : when using multiple UPDATES in a single query, will the WHERE clause use the current or updated field values?
body: <p>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.</p>
  
  <p>I am updating a table with new values based on a spreadsheet - which means I'm using multiple <code>UPDATE</code> statements in a single query:</p>
  
  <pre><code>UPDATE Asset SET AssetID = 'NewID' WHERE AssetID = 'OldID'
  </code></pre>
  
  <p>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;</p>
  
  <pre><code>UPDATE Asset SET AssetID = '001' WHERE AssetID = '111'
  UPDATE Asset SET AssetID = '002' WHERE AssetID = '112'
  UPDATE Asset SET AssetID = '003' WHERE AssetID = '113'
  </code></pre>
  
  <p>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 <code>WHERE</code> clause, I may end up inadvertently updating multiple rows.  </p>
  
  <p>For example:</p>
  
  <pre><code>UPDATE Asset SET AssetID = '001' WHERE AssetID = '111'
  UPDATE Asset SET AssetID = '002' WHERE AssetID = '001'
  UPDATE Asset SET AssetID = '003' WHERE AssetID = '002'
  </code></pre>
  
  <p>Now imagine, that prior to me running the above query, I have the following table:</p>
  
  <pre><code>AssetID
  111
  001
  002
  </code></pre>
  
  <p>What I want to happen, is for the <code>WHERE</code> clause in my <code>UPDATE</code> statements, to use only the <code>AssetID</code> that the column <em>CURRENTLY</em> has - i.e. the <code>ID</code> in use prior to running the query. </p>
  
  <p>This would give me the table below:</p>
  
  <pre><code>AssetID
  001
  002
  003
  </code></pre>
  
  <p>What I'm worried will <em>ACTUALLY</em> happen, is that it'll process the updates in sequence, and use the updated <code>ID</code> in the <code>WHERE</code> clause, which means my table will instead look like this:</p>
  
  <pre><code>AssetID
  003
  003
  003
  </code></pre>
  
  <p>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.</p>
  
  <p>Can someone confirm?</p>