Sample Data
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
Desired Result
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
As you can see, I have an instalment_num
column which should show the number/index of each row belonging to the order_id
, determined by the date_due ASC, id ASC
order.
How can I update the instalment_num
column like this?
Additional Notes
The date_due
column is not unique, and there may be many id
s or order_id
s with the exact same timestamp.
If the timestamp is the same for two rows belonging to the same order_id
, it should order them by id
as a fallback.
I require a query which will update this column.
> Is it possible to use Row_Number() function in an UPDATE statement? Yes. > However, I'm getting this error: "Subquery returned more than 1 value.
The ROW_NUMBER() function in MySQL is used to returns the sequential number for each row within its partition. It is a kind of window function. The row number starts from 1 to the number of rows present in the partition.
There are a couple of ways to do it. INSERT INTO students (id, score1, score2) VALUES (1, 5, 8), (2, 10, 8), (3, 8, 3), (4, 10, 7) ON DUPLICATE KEY UPDATE score1 = VALUES(score1), score2 = VALUES(score2);
This is how I would do it:
SELECT a.id,
a.order_id,
COUNT(b.id)+1 AS instalment_num,
a.date_due
FROM sample_data a
LEFT JOIN sample_data b ON a.order_id=b.order_id AND (a.date_due>b.date_due OR (a.date_due=b.date_due AND a.id>b.id))
GROUP BY a.id, a.order_id, a.date_due
ORDER BY a.order_id, a.date_due, a.id
UPDATE version attempt:
UPDATE sample_data
LEFT JOIN (SELECT a.id,
COUNT(b.id)+1 AS instalment_num
FROM sample_data a
JOIN sample_data b ON a.order_id=b.order_id AND (a.date_due>b.date_due OR (a.date_due=b.date_due AND a.id>b.id))
GROUP BY a.id) c ON c.id=sample_data.id
SET sample_data.instalment_num=c.instalment_num
For the numbering to begin with 1:
UPDATE sample_data
LEFT JOIN (SELECT a.id,
COUNT(b.id) AS instalment_num
FROM sample_data a
JOIN sample_data b ON a.order_id = b.order_id AND (a.date_due > b.date_due OR (a.date_due=b.date_due AND a.id + 1 > b.id))
GROUP BY a.id) c ON c.id = sample_data.id
SET sample_data.instalment_num = c.instalment_num
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With