title: MySQL update value from the same table with count
body: <p>What I want to do is to set every patient its unique patient code which starts with 1 and it's not based on row id. Id only specifies order. Something like this:</p>
  
  <pre><code>patient_id  patient_code
      2           1
      3           2
      4           3
  </code></pre>
  
  <p>This is my query:</p>
  
  <pre><code>UPDATE patients p1
  SET p1.patient_code = (
      SELECT COUNT( * ) 
      FROM patients p2
      WHERE p2.patient_id &lt;= p1.patient_id 
  )
  </code></pre>
  
  <p>But it is throwing error:</p>
  
  <pre><code>#1093 - You can't specify target table 'p1' for update in FROM clause
  </code></pre>
  
  <p>I found this thread: <a href="https://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause">Mysql error 1093 - Can't specify target table for update in FROM clause</a>.<br />But I don't know how to apply approved answer this to work with subquery WHERE which is necessary for COUNT.</p>