title: How to update from select with a Join
body: <p>How can I update a table that is also present in a subquery? Do I have to do it in 2 stages? (create a temporary table - put the selected data in it and then update the final table)</p>
  
  <p>I am trying to update the invoiceLine table with the label of the network for each CTN.</p>
  
  <p>The end result would be:</p>
  
  <ul>
  <li><p>invoiceLine</p>
  
  <pre><code>ctn       network
  1234      network1
  2345      network2
  3456      network1
  </code></pre></li>
  </ul>
  
  <p>I have the following tables:</p>
  
  <ul>
  <li><p>invoiceLine</p>
  
  <pre><code>ctn       network
  1234      null
  2345      null
  3456      null
  </code></pre></li>
  <li><p>terminal</p>
  
  <pre><code>ctn       network
  1234      1
  2345      2
  3456      1
  </code></pre></li>
  <li><p>network</p>
  
  <pre><code>id        label
  1         network1
  2         network2
  </code></pre></li>
  </ul>
  
  <p>I can run a select but I'm not sure how to update with a join:</p>
  
  <pre><code>update invoiceLine 
  inner join terminal on terminal.ctn = invoiceLine.ctn 
  set invoiceLine.network = 
  (
    select network.label 
    from invoiceLine 
    inner join terminal on terminal.ctn = invoiceLine.ctn 
    inner join network on network.id = terminal.network
  ) 
  where invoiceLine.ctn = terminal.ctn
  </code></pre>
  
  <p>but MySQL throws a </p>
  
  <blockquote>
    <p>Error Code: 1093. You can't specify target table 'invoiceLine' for update in FROM clause</p>
  </blockquote>