1

I have a table as

id  value
1   music
2 
3   movie
4   
5
6   book
7
8

Some values are missing, and I want to assign the value of previous row to produce

id  value
1   music
2   music
3   movie
4   movie
5   movie
6   book
7   book
8   book

1 Answer 1

4

Sample schema and data

create table tbl (
  id int,
  value varchar(10)
  );
insert into tbl select
  1,   'music' union all select
  2,   ''  union all select
  3,   'movie' union all select
  4,   '' union all select
  5,   '' union all select
  6,   'book' union all select
  7,   '' union all select
  8,   '';

Query (SQL Fiddle)

update tbl join (
    select curr.id, (
        select prior.value
          from tbl prior
         where prior.value > ''
           and prior.id < curr.id
      order by prior.id desc
       limit 1) value
      from tbl curr
    where ifnull(curr.value,'') = ''
  ) g on g.id = tbl.id
set tbl.value = g.value;

Results

select * from tbl

| ID | VALUE |
--------------
|  1 | music |
|  2 | music |
|  3 | movie |
|  4 | movie |
|  5 | movie |
|  6 |  book |
|  7 |  book |
|  8 |  book |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.