MySQL fill empty column

Question

I have the following table: MyTable(id, group_column, column_to_fill)

Currently 'column_to_fill' is empty and I want to fill it with increasing integers. The values should start from 0 for every value of group_column.

My table looks like this:

+-----------------------------------+------------+----------------+
|               id                  |group_column| column_to_fill |
+-----------------------------------+------------+----------------+
| 9b71dd5c-d8a6-461c-b1f3-af1e4b1d  | Value1     |     null       |
| 38886977-0f34-4059-b192-f94f5aed  | Value1     |     null       |
| d98e16da-a919-4242-baf8-dbbef636  | Value2     |     null       |
| e1ab88a9-3307-49a6-b37d-72cdb5da  | Value2     |     null       |
| 75174dcb-eb74-4c13-80a1-1b21905d  | Value2     |     null       |
+-----------------------------------+------------+----------------+

I want it to look like this:

+-----------------------------------+------------+----------------+
|               id                  |group_column| column_to_fill |
+-----------------------------------+------------+----------------+
| 9b71dd5c-d8a6-461c-b1f3-af1e4b1d  | Value1     |     0          |
| 38886977-0f34-4059-b192-f94f5aed  | Value1     |     1          |
| d98e16da-a919-4242-baf8-dbbef636  | Value2     |     0          |
| e1ab88a9-3307-49a6-b37d-72cdb5da  | Value2     |     1          |
| 75174dcb-eb74-4c13-80a1-1b21905d  | Value2     |     2          |
+-----------------------------------+------------+----------------+

How can I update it in MySQL?

you can create a stored procedure, using loop. Look at this answer: https://stackoverflow.com/a/16350693/2469308 — Madhur Bhaiya, Aug 11 '18 at 09:18

score 2 · Accepted Answer · answered Aug 11 '18 at 11:23

I would recommend approaching this as:

set @rn := -1;
set @g := '';

update t
    set column_to_fill = if(@g = group_column, @rn := @rn + 1,
                            if(@g := group_column, @rn := 0, @rn := 0)
                          )
    order by group_column;

When using variables in MySQL, it is really important to remember that the order of evaluation of expressions is not guaranteed. So, you need to put all references and assignments of a variable into the same expression.

In MySQL 8+, you can do:

update t join
       (select t.*, row_number() over (partition by group_column order by group_column) as seqnum
        from t
       ) tt
       on t.id = tt.id
    set column_to_fill = seqnum - 1;

score 1 · Answer 2 · answered Aug 11 '18 at 09:17

You can achieve the same effect using this simple SELECT (further, you can use ut to update your table):

select @lag := '', @i := 0;

select group_column, rn from (
  select @lag,
         case when group_column = @lag then @i := @i + 1 else @i := 0 end rn,
         @lag := group_column, group_column
  from tbl
) a

UPDATE statement:

select @lag := '', @i := 0;
update tbl
join (
  select id,
         @lag,
         case when group_column = @lag then @i := @i + 1 else @i := 0 end rn,
         @lag := group_column, group_column
  from tbl
) t on tbl.id = t.id and tbl.group_column = t.group_column
set tbl.column_to_fill = t.rn

MySQL fill empty column

2 Answers2