MySQL - Update table with row number per group
<p><strong>Sample Data</strong></p>

<pre><code>id  |  order_id  |  instalment_num  | date_due
---------------------------------------------------------
1   |  10000     |  1               | 2010-07-09 00:00:00
2   |  10000     |  1               | 2010-09-06 11:39:56
3   |  10001     |  1               | 2014-04-25 15:46:52
4   |  10002     |  1               | 2010-01-11 00:00:00
5   |  10003     |  1               | 2010-01-04 00:00:00
6   |  10003     |  1               | 2016-05-31 00:00:00
7   |  10003     |  1               | 2010-01-08 00:00:00
8   |  10003     |  1               | 2010-01-06 09:06:26
9   |  10004     |  1               | 2010-01-11 11:25:07
10  |  10004     |  1               | 2010-01-12 07:06:42
</code></pre>

<p><strong>Desired Result</strong></p>

<pre><code>id  |  order_id  |  instalment_num  | date_due
---------------------------------------------------------
1   |  10000     |  1               | 2010-07-09 00:00:00
2   |  10000     |  2               | 2010-09-06 11:39:56
3   |  10001     |  1               | 2014-04-25 15:46:52
4   |  10002     |  1               | 2010-01-11 00:00:00
5   |  10003     |  1               | 2010-01-04 00:00:00
8   |  10003     |  2               | 2010-01-06 09:06:26
7   |  10003     |  3               | 2010-01-08 00:00:00
6   |  10003     |  4               | 2016-05-31 00:00:00
9   |  10004     |  1               | 2010-01-11 11:25:07
10  |  10004     |  2               | 2010-01-12 07:06:42
</code></pre>

<p>As you can see, I have an <code>instalment_num</code> column which should show the number/index of each row belonging to the <code>order_id</code>, determined by the <code>date_due ASC, id ASC</code> order.</p>

<p>How can I update the <code>instalment_num</code> column like this?</p>

<p><strong>Additional Notes</strong></p>

<ul>
<li><p>The <code>date_due</code> column is not unique, and there may be many <code>id</code>s or <code>order_id</code>s with the exact same timestamp.</p></li>
<li><p>If the timestamp is the same for two rows belonging to the same <code>order_id</code>, it should order them by <code>id</code> as a fallback.</p></li>
<li><p>I require a query which will update this column.</p></li>
</ul>