Sometimes you may need to update rows of a table in MySQL, based on previous row. This is a common requirement for many database developers but can prove to be tricky. By default, MySQL UPDATE statement allows you to update a column based on that row’s values and not other rows. In this article, we will learn how to update row based on previous row in MySQL.
How to Update Row Based on Previous Row in MySQL
Let us say you have the following table Items
ID | Image 1 | 10 2 | 11 3 | 4 | 5 |
As you can see, some of the Image column’s values are empty. Let us say you want to update the above Items table such that each row’s Image column has value that is 1 more than the value in previous row.
ID | Image 1 | 10 2 | 11 3 | 12 4 | 13 5 | 14
Here is the MySQL query to do this.
UPDATE Items AS i1 JOIN ( SELECT ID, @n := @n + 1 AS Image FROM Items CROSS JOIN (SELECT @n := (SELECT MAX(Image) FROM Items)) AS v WHERE Image IS NULL ORDER BY ID ) AS i2 ON i1.ID = i2.ID SET i1.Image = i2.Image;
Let us look at the query in detail. In the above query, we set a temporary variable whose value is one more than the maximum value of Image column.
SELECT @n := (SELECT MAX(Image) FROM Items)
We use the above variable and increment it by one for each row, and store its value for each row.
@n := @n + 1 AS Image
Here is the complete derived table where all this happens.
SELECT ID, @n := @n + 1 AS Image FROM Items CROSS JOIN (SELECT @n := (SELECT MAX(Image) FROM Items)) AS v WHERE Image IS NULL ORDER BY ID
Finally, we merge the above derived table with the original Items table to update Image column.
UPDATE Items AS i1 JOIN ( SELECT ID, @n := @n + 1 AS Image FROM Items CROSS JOIN (SELECT @n := (SELECT MAX(Image) FROM Items)) AS v WHERE Image IS NULL ORDER BY ID ) AS i2 ON i1.ID = i2.ID SET i1.Image = i2.Image;
In this article, we have learnt how to update column based on previous row in MySQL.
Also read:
How to Disable Commands in Linux
How to Install Printer in Ubuntu Via GUI
How to Increase SSH Connection Limit in Linux
How to Disable Root Login in Linux
How to Disable Su Access to Sudo in Linux