title: Update & Select a MySQL table in FIFO queue with non-unique identifiers
body: <p>I have this table <code>documents</code> based on client's set of data. Only provided key is <code>documentId</code>, but it's <strong>non-unique</strong> (and <code>rowId</code> is mine, not client's).</p>
  <p>This table also shows which documents have been processed and which have not, plus some additional data. <code>isProcessed</code> is a generated column (<code>isProcessed bool as (processed is not null)</code>) for indexing purposes.</p>
  <pre><code>SELECT * FROM documents;
  
  | rowId | documentId | received            | processed           | user  | isProcessed |
  | ----- | ---------- | ------------------- | ------------------- | ----- | ----------- |
  | 1     | 10020      | 2020-11-27 15:01:00 | 2020-11-27 17:17:17 | user1 | 1           |
  | 2     | 10020      | 2020-11-27 15:02:00 | 2020-11-27 17:17:17 | user1 | 1           |
  | 3     | 10021      | 2020-11-27 15:03:00 | 2020-11-27 17:17:17 | user1 | 1           |
  | 4     | 10022      | 2020-11-27 15:04:00 | 2020-11-27 17:17:17 | user1 | 1           |
  | 5     | 10022      | 2020-11-27 15:05:00 | 2020-11-27 17:17:17 | user1 | 1           |
  | 6     | 10022      | 2020-11-27 15:06:00 |                     |       | 0           |
  | 7     | 10022      | 2020-11-27 15:07:00 |                     |       | 0           |
  | 8     | 10022      | 2020-11-27 15:08:00 |                     |       | 0           |
  | 9     | 10023      | 2020-11-27 15:09:00 |                     |       | 0           |
  | 10    | 10024      | 2020-11-27 15:10:00 |                     |       | 0           |
  | 11    | 10024      | 2020-11-27 15:11:00 |                     |       | 0           |
  | 12    | 10025      | 2020-11-27 15:12:00 |                     |       | 0           |
  | 13    | 10025      | 2020-11-27 15:13:00 |                     |       | 0           |
  | 14    | 10025      | 2020-11-27 15:14:00 |                     |       | 0           |
  | 15    | 10025      | 2020-11-27 15:15:00 |                     |       | 0           |
  </code></pre>
  <p>Then, when employees process some documents, they pass their <code>documentId's</code> into an app, generating a JSON like this:</p>
  <pre class="lang-json prettyprint-override"><code>{
    &quot;user&quot;: &quot;user2&quot;,
    &quot;documents&quot;: [
        10022,
        10022,
        10023,
        10024,
        10025,
        10025,
        10025
    ]
  }
  </code></pre>
  <h2>Now I need to figure out how to:</h2>
  <p><strong>a) update</strong> <code>documents</code> to set <code>processed</code> and <code>user</code> for first matching unprocessed documents, but no more than that!</p>
  <p>That's 2 x 10022 (rows 6, 7), 1 x 10023 (row 9), 1 x 10024 (row 10), 3 x 10025 (rows 12, 13, 14).</p>
  <p>What I've got so far is:</p>
  <pre class="lang-sql prettyprint-override"><code>SET @processedTS = NOW();
  
  UPDATE documents SET user = 'user2', processed = @processedTS WHERE documentId = 10022 AND !isProcessed ORDER BY rowId LIMIT 2;
  UPDATE documents SET user = 'user2', processed = @processedTS WHERE documentId = 10023 AND !isProcessed ORDER BY rowId LIMIT 1;
  UPDATE documents SET user = 'user2', processed = @processedTS WHERE documentId = 10024 AND !isProcessed ORDER BY rowId LIMIT 1;
  UPDATE documents SET user = 'user2', processed = @processedTS WHERE documentId = 10025 AND !isProcessed ORDER BY rowId LIMIT 3;
  </code></pre>
  <p>Is there a smarter way to approach this than generate multiple update statements?</p>
  <p><strong>b) select</strong> the updated documents to get their additional information and make a results file to send to the client</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT * FROM documents WHERE processed = @processedTS;
  </code></pre>
  <p>Is there a smarter way to select the updated data?</p>
  <h2>Expected results</h2>
  <p>a) table <code>documents</code> after updates</p>
  <pre><code>| rowId | documentId | received            | processed           | user  | isProcessed |
  | ----- | ---------- | ------------------- | ------------------- | ----- | ----------- |
  | 1     | 10020      | 2020-11-27 15:01:00 | 2020-11-27 17:17:17 | user1 | 1           |
  | 2     | 10020      | 2020-11-27 15:02:00 | 2020-11-27 17:17:17 | user1 | 1           |
  | 3     | 10021      | 2020-11-27 15:03:00 | 2020-11-27 17:17:17 | user1 | 1           |
  | 4     | 10022      | 2020-11-27 15:04:00 | 2020-11-27 17:17:17 | user1 | 1           |
  | 5     | 10022      | 2020-11-27 15:05:00 | 2020-11-27 17:17:17 | user1 | 1           |
  | 6     | 10022      | 2020-11-27 15:06:00 | 2020-11-27 19:34:58 | user2 | 1           |
  | 7     | 10022      | 2020-11-27 15:07:00 | 2020-11-27 19:34:58 | user2 | 1           |
  | 8     | 10022      | 2020-11-27 15:08:00 |                     |       | 0           |
  | 9     | 10023      | 2020-11-27 15:09:00 | 2020-11-27 19:34:58 | user2 | 1           |
  | 10    | 10024      | 2020-11-27 15:10:00 | 2020-11-27 19:34:58 | user2 | 1           |
  | 11    | 10024      | 2020-11-27 15:11:00 |                     |       | 0           |
  | 12    | 10025      | 2020-11-27 15:12:00 | 2020-11-27 19:34:58 | user2 | 1           |
  | 13    | 10025      | 2020-11-27 15:13:00 | 2020-11-27 19:34:58 | user2 | 1           |
  | 14    | 10025      | 2020-11-27 15:14:00 | 2020-11-27 19:34:58 | user2 | 1           |
  | 15    | 10025      | 2020-11-27 15:15:00 |                     |       | 0           |
  </code></pre>
  <p>b) results file</p>
  <pre class="lang-json prettyprint-override"><code>[
      { &quot;documentId&quot;: 10022, &quot;received&quot;: &quot;2020-11-27 15:06:00&quot;, &quot;processed&quot;: &quot;2020-11-27 19:34:58&quot;, &quot;user&quot;: &quot;user2&quot;},
      { &quot;documentId&quot;: 10022, &quot;received&quot;: &quot;2020-11-27 15:07:00&quot;, &quot;processed&quot;: &quot;2020-11-27 19:34:58&quot;, &quot;user&quot;: &quot;user2&quot;},
      { &quot;documentId&quot;: 10023, &quot;received&quot;: &quot;2020-11-27 15:09:00&quot;, &quot;processed&quot;: &quot;2020-11-27 19:34:58&quot;, &quot;user&quot;: &quot;user2&quot;},
      { &quot;documentId&quot;: 10024, &quot;received&quot;: &quot;2020-11-27 15:10:00&quot;, &quot;processed&quot;: &quot;2020-11-27 19:34:58&quot;, &quot;user&quot;: &quot;user2&quot;},
      { &quot;documentId&quot;: 10025, &quot;received&quot;: &quot;2020-11-27 15:12:00&quot;, &quot;processed&quot;: &quot;2020-11-27 19:34:58&quot;, &quot;user&quot;: &quot;user2&quot;},
      { &quot;documentId&quot;: 10025, &quot;received&quot;: &quot;2020-11-27 15:13:00&quot;, &quot;processed&quot;: &quot;2020-11-27 19:34:58&quot;, &quot;user&quot;: &quot;user2&quot;},
      { &quot;documentId&quot;: 10025, &quot;received&quot;: &quot;2020-11-27 15:14:00&quot;, &quot;processed&quot;: &quot;2020-11-27 19:34:58&quot;, &quot;user&quot;: &quot;user2&quot;}
  ]
  </code></pre>
  <p>(I know how to make that JSON, I'm looking for a smart way to select the updated data)</p>
  <h2>My question is...</h2>
  <p>In production, <code>documents</code> will have some 300k + rows, and each save run will affect hundreds of rows.</p>
  <p><strong>Is there a smarter way to update in bulk &amp; select updated data?</strong></p>
  <p>Ideally, I'm looking for a way to pass <code>[10022, 10022, 10023, 10024, 10025, 10025, 10025]</code> onto the update in a way that will respect FIFO order and match only first listed documents, not more. Running hundreds of update statements does not seem ideal.</p>
  <p>I'm using MySQL 8.0. The app is written in javascript (typescript) run on Node.js v12. I'm only looking for smarter MySQL solution. For suggestions with some logic in app layer, the answer doesn't have to be in JS, any pseudocode is appreciated too.</p>
  <p>Fiddle w/ table schema here: <a href="https://www.db-fiddle.com/f/ecYLwEGpwZpPtec6VwzMBF/1" rel="nofollow noreferrer">https://www.db-fiddle.com/f/ecYLwEGpwZpPtec6VwzMBF/1</a></p>
  <p>Thank you very much.</p>