CODEWITHSUNDEEP
mysql
user4920811
user4920811

Reputation:

how to put value of the upper row in the bottom rows

I have a table like this:

+-----+------------+
| id  |    name    |
|-----|------------|
|  1  |     ali    |
|-----|------------|
|  2  |            |
|-----|------------|
|  3  |            |
|-----|------------|
|  4  |    peter   |
|-----|------------|
|  5  |            |
+-----+------------+

I want to put value of upper row the bottom rows. (If the bottom row was empty). Some thing like this:

+-----+------------+
| id  |    name    |
|-----|------------|
|  1  |     ali    |
|-----|------------|
|  2  |     ali    |
|-----|------------|
|  3  |     ali    |
|-----|------------|
|  4  |    peter   |
|-----|------------|
|  5  |    peter   |
+-----+------------+

Is it possible to do that ?

Upvotes: 0

Views: 58

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270473

How about this?

update t
    set name = (case when name is not null
                     then @name := name
                     else @name
                end)
    order by id;

It seems like the easiest method using MySQL.

Upvotes: 1

Strawberry
Strawberry

Reputation: 33945

e.g.:

UPDATE my_table a
  JOIN 
     ( SELECT x.*
            , MAX(y.name) new_name 
         FROM my_table x 
         JOIN my_table y 
           ON y.id <= x.id 
        GROUP 
           BY x.id
     ) b
    ON b.id = a.id SET a.name = b.new_name;

or

UPDATE my_table a
  JOIN 
     ( SELECT x.*
            , CASE WHEN name IS NULL THEN @name ELSE @name:=name END new_name 
         FROM my_table x
            , (SELECT @name:=NULL) vars 
        ORDER 
           BY id
     ) b
    ON b.id = a.id
   SET a.name = b.new_name;

Upvotes: 1

Blindy
Blindy

Reputation: 67437

Sure, you can use something like this:

update t
set name=case 
           when name is null then (select name 
                                   from (select * from t) it 
                                   where it.id<t.id and it.name is not null 
                                   order by id desc
                                   limit 1)
           else name
         end

You can see it in action here, but it should be pretty self evident how it works. The only funny thing is that inner select, that's to work around an absurd limitation of MySql where it won't let you select from the table you're updating.

Upvotes: 1

Related Questions