title: Can a subquery inside a SQL update fetch rows which have just been updated?
body: <p>Having a collection of publications, I want to assign a different release date for each one per author. For doing this I am subtracting to all the dates, from publication's date until yesterday, the already taken dates for that author.</p>
  <p>The problem of this update is that the current record depends on the assignation of the previous one. Eg: if there is already a feature assigned to April 2nd, new features on that day will be pushed to the 3rd or beyond. But if there are two unassigned features April 2nd, they will be both assigned to the same day.</p>
  <pre><code>UPDATE publications pub
  SET pub.release_date = (
      SELECT all.Dates 
      FROM ( # This generates all dates between publication date until yesterday
          SELECT curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a) + (1000 * d.a) ) DAY as Dates
          FROM (SELECT 0 as a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) as a
          CROSS JOIN (SELECT 0 as a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) as b
          CROSS JOIN (SELECT 0 as a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) as c
          CROSS JOIN (SELECT 0 as a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) as d
      ) all
      WHERE all.Dates &gt; DATE(pub.date)
      AND all.Dates &lt; curdate()
      AND all.Dates NOT IN ( # Already taken dates for this author
          SELECT DISTINCT(DATE(taken.release_date))
          FROM (SELECT * FROM publications) as taken
          WHERE taken.author_id = pub.author_id
          AND taken.release_date IS NOT NULL
          )
      ORDER BY Date ASC
      limit 1
  )
  WHERE pub.release_date is null
  AND pub.type = 'feature';
  </code></pre>
  <p>I <a href="https://orahow.com/how-update-statement-works-internally-in-oracle/" rel="nofollow noreferrer">read</a> that the way SQL works (simplifying here) is fetching a dataset to the buffer, altering it and then storing. Guess MySQL does something similar. This mismatch seems to happen because the subquery is not reading from the data buffer that we are updating but from the original dataset.</p>
  <p>MySQL doesn't allow <a href="https://stackoverflow.com/a/6258586/6452888">PostgreSQL update syntax</a>:</p>
  <pre><code>UPDATE ...
  SET ...
  FROM &lt;- 
  WHERE ...;
  </code></pre>
  <p>Can a subquery inside a SQL update fetch rows which have just been updated?</p>