title: Updating table is the same as the table in the sub-query
body: <pre><code>UPDATE MyTable SET Status = (SELECT CASE WHEN COUNT(*) &gt; 0
                              THEN 0          
                              ELSE 1
                              END 
                      FROM MyTable 
                      WHERE Status = 1 )
  WHERE RowNumber BETWEEN 1 AND 5
  ORDER BY RowNumber
  </code></pre>
  
  <p>What I want to do is there will be only one row in MyTable with Status = 1.
  While I update RowNumber 1 to 5, if there has been an existing record with Status = 1,
  those RowNumber 1 to 5 will be updated with 0. Otherwise, only RowNumber 1 will be updated to 0 and RowNumber 2 to 5 will be set 0.</p>
  
  <p>But aforementioned query is not working. I guess sub-query is run before the whole query and the result from the sub-query is static which means not giving new result as one record after another is being updated.</p>
  
  <p>With this query, if there is no row in the table with Status = 1 then all RowNumber 1 to 5 are updated with Status = 1. This is not what I am expecting.</p>