title: Update row with self reference in a sub query
body: <p>I have a table with about 7000 rows where about 4000 have an error caused of a bug. (The location is <code>'null'</code> <strong>not</strong> <code>NULL</code> and should be somthing else.) Now I'm trying to fix the data which is for almost all rows possible.</p>
  
  <pre><code>UPDATE `timelog` t
  SET `location`=(SELECT location
                  FROM timelog tl
                  WHERE tl.end_ts=t.end_ts AND location != 'null'
                  ORDER BY tl.log_id DESC
                  LIMIT 0,1) -- Just to make sure that I get 1 or 0 results
  WHERE end_ts &gt; '2012-01-01 00:00:00' AND location = 'null';
  </code></pre>
  
  <p>But I get the error:</p>
  
  <blockquote>
    <p>#1093 - You can't specify target table 't' for update in FROM clause </p>
  </blockquote>
  
  <p>Well it seems that I cannot access the row itself while updating how can I slove that problem?</p>
  
  <p>Maybe with a temp table, but that seems to be a little overhead also I have to manage to copy all unrelated rows too.</p>
  
  <p>I also tried to use instat a join like explained in <a href="https://stackoverflow.com/a/7148180/995926">this answer</a>, but I need to restrict the selected row.</p>
  
  <hr>
  
  <p>Based on the answers I tried my own solution with a temp view:</p>
  
  <pre><code>CREATE OR REPLACE VIEW right_locations AS
  SELECT l.*, t.end_ts, t.location, (SELECT location FROM timelog tl WHERE tl.end_ts=t.end_ts AND location != 'null' ORDER BY tl.log_id DESC LIMIT 0,1) AS "possible", t.end_location
  FROM `log` l
  JOIN timelog t ON t.log_id=l.log_id
  WHERE l.action_id =7 AND l.ts &gt; '2012-01-01 00:00:00'
  ORDER BY end_location;
  
  UPDATE timelog t
  JOIN right_locations r ON r.log_id=t.log_id
  SET t.location = r.possible
  WHERE t.end_ts &gt; '2012-01-01 00:00:00' AND t.location = 'null';
  </code></pre>