CODEWITHSUNDEEP
mysqlsqlsql-update
user1552545
user1552545

Reputation: 1331

MySQL fill empty column

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?

Upvotes: 1

Views: 1570

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

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;

Upvotes: 2

Michał Turczyn
Michał Turczyn

Reputation: 37367

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

Upvotes: 1

Related Questions