CODEWITHSUNDEEP
mysql
Linesofcode
Linesofcode

Reputation: 5891

MySQL update column based on previous row (same column)

I have the following data:

ID | Image
1  |  10
2  |  11
3  |  
4  |
5  |

And I would like to update the missing values with the value of the row before plus one.

The final output should be:

ID | Image
1  |  10
2  |  11
3  |  12
4  |  13
5  |  14

I thought about a select during the update, but it doesn't work.

UPDATE items AS item1
SET item1.image = (SELECT image 
                   FROM items AS item2 
                   WHERE item2.id < item1.id 
                   ORDER BY item2.id DESC LIMIT 1) + 1

Upvotes: 4

Views: 5937

Answers (4)

morakabi
morakabi

Reputation: 1

I have same problem, and I use simple Update with variable (@)

update items,(select @n := 10) v set `Image`=@n:=@n+1 order by ID asc;

I hope to be useful this query :D

Upvotes: 0

Dylan Su
Dylan Su

Reputation: 6065

Try this solution using a user variable, with a complete demo as below.

SQL:

-- data
create table items(ID int, Image int);
insert into items values
(1, 10),(2, NULL),(3, NULL),(4, NULL),(5, NULL);
SELECT * FROM items;

-- SQL needed
SET @i = 0;
UPDATE items
SET Image = (IF(Image IS NULL OR Image = '',
                @i:=@i+1,
                @i:=Image
                ));
SELECT * FROM items;

Output:

mysql> SELECT * FROM items;
+------+-------+
| ID   | Image |
+------+-------+
|    1 |    10 |
|    2 |  NULL |
|    3 |  NULL |
|    4 |  NULL |
|    5 |  NULL |
+------+-------+
5 rows in set (0.00 sec)

mysql>
mysql> SET @i = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE items
    -> SET Image = (IF(Image IS NULL OR Image = '',
    ->                 @i:=@i+1,
    ->                 @i:=Image
    ->                 ));
Query OK, 4 rows affected (0.00 sec)
Rows matched: 5  Changed: 4  Warnings: 0

mysql> SELECT * FROM items;
+------+-------+
| ID   | Image |
+------+-------+
|    1 |    10 |
|    2 |    11 |
|    3 |    12 |
|    4 |    13 |
|    5 |    14 |
+------+-------+
5 rows in set (0.00 sec)

Upvotes: 3

Sevle
Sevle

Reputation: 3119

Given that the tables you present are not a sample I'd do something hacky and simple like this:

Update items Set items.Image=items.id+9 WHERE items.Image is NULL;

Oh god, I smell the incoming downvotes!

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can use an UPDATE with a JOIN to a derived table for 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;

The derived table uses variables in order to calculate the Image values of the records having NULLs.

Demo here

Upvotes: 3

Related Questions