title: Can we wirte with(nolock) in update statement joining with other tables
body: <p>I have two tables, let say Main,transactions table </p>
  
  <p>Main : </p>
  
  <pre><code>mainID Name  Active
  1  sharath  1
  2  kiran    0
  3  ashish   1
  </code></pre>
  
  <p>Transaction : </p>
  
  <pre><code>TransID TransType status MainID IsActive
  101      D         22    1       0
  102      R         27    2       1
  103      R         32    2       1
  104      D         11    1       0
  105      R         43    3       0
  </code></pre>
  
  <p>which update statement is preferable in these :</p>
  
  <p>1)</p>
  
  <pre><code>Update TR
  set  status  = 0,
       Isactive = 0
  from Transaction TR
  inner join main MN with(nolock) on MN.MainID = TR.MainID 
  where MN.Isactive = 0
  </code></pre>
  
  <p>2)</p>
  
  <pre><code>Update TR
  set  status  = 0,
       Isactive = 0
  from Transaction TR
  inner join main MN on MN.MainID = TR.MainID 
  where MN.Isactive = 0
  </code></pre>
  
  <p>In first query, I made inner join with Main and used <code>with(nolock)</code> as I want to update the records for those MainID where Isactive = 0</p>
  
  <p>In second query, without using <code>with(nolock)</code> I have written update statement.</p>
  
  <p>which one is preferrable, n will do update without any errors in future also.</p>