update columns based on previous row

How to Update Row Based on Previous Row in MySQL

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

Leave a Reply

Your email address will not be published.