title: UPDATE + WITH (ROWLOCK) + CTE
body: <p>I can't find any documentation about syntax for T-SQL statement:
  I need to make an <code>WITH (ROWLOCK)</code> UPDATE on a CTE result.</p>
  
  <p>Something like: (so updated will be top1000 table1.col2. Statement <code>WITH (ROWLOCK)</code> during an UPDATE on rows of table1 is crucial)</p>
  
  <pre><code>    ;WITH CTE AS 
      ( 
          SELECT TOP(1000) table1.col2
          FROM  table1 INNER JOIN table2 ON table1.id = table2.id    
      ) 
      UPDATE CTE WITH (ROWLOCK)
      SET col2 = 1
  </code></pre>
  
  <p>The above statement is probably syntactically correct, however if someone will find such example, please give me a link.</p>
  
  <p>BUT: my full SQL looks like below. During execute I get error:</p>
  
  <blockquote>
    <p>Conflicting locking hints are specified for table "table1". This may be caused by a conflicting hint specified for a view.</p>
  </blockquote>
  
  <p>Why can't I use <code>WITH (NOLOCK)</code> for selecting and <code>WITH (ROWLOCK)</code> on updating?</p>
  
  <pre><code>;WITH CTE AS 
  ( 
      SELECT TOP(5) table1.col2
      FROM table1 WITH (NOLOCK) INNER JOIN table2 WITH (NOLOCK) ON table1.id = table2.id 
      WHERE table1.col3 = 2
      ORDER BY table1.id    
  ) 
  UPDATE CTE WITH (ROWLOCK)
  SET col2 = 1
  </code></pre>