MySQL Update with derived tables and ORDER BY -

this question follow question link. have table person (id) , 1 characteristic (var0) @ different timepoints t. @ timepoints characteristic missing , fill gaps former value. here example of table:

+---+---+----+            +----+---+------+------+------------------+ |id | t |var0|            | id | t | var0 | var1 | @prev_id   := id | +---+---+----+            +----+---+------+------+------------------+ | 1 | 1 |  |            |  1 | 1 |    |    |                1 | | 1 | 3 | \n |            |  1 | 3 | \n   |    |                1 | | 1 | 7 | \n |            |  1 | 7 | \n   |    |                1 | | 1 | 8 | b  |            |  1 | 8 | b    | b    |                1 | | 1 | 9 | \n |            |  1 | 9 | \n   | b    |                1 | | 2 | 2 | \n |            |  2 | 2 | \n   | \n   |                2 | | 2 | 4 | u  |            |  2 | 4 | u    | u    |                2 | | 2 | 5 | u  |            |  2 | 5 | u    | u    |                2 | | 2 | 6 | \n |            |  2 | 6 | \n   | u    |                2 | | 2 | 7 | \n |            |  2 | 7 | u    | u    |                2 | | 2 | 8 | v  |            |  2 | 8 | v    | v    |                2 | | 2 | 9 | \n |            |  2 | 9 | \n   | v    |                2 | +---+---+----+            +----+---+------+------+------------------+ 

the left table orignal x1 table , right table requested table. here code result:

drop table if exists test01.x1; create table test01.x1 (   id   integer , t    integer , var0 char(1) ) engine = innodb  default character set = utf8 collate = utf8_unicode_ci ;  insert test01.x1(id,t,var0) values ( 1,1,'a' ) ,(1,3,null) ,(1,7,null) ,(1,8,'b' ) ,(1,9,null) ,(2,2,null) ,(2,4,'u' ) ,(2,5,'u' ) ,(2,6,null) ,(2,7,'u') ,(2,8,'v' ) ,(2,9,null) ;  drop table if exists test01.x2; create table test01.x2 select id, t        , var0        , @prev_var0 := cast(if(id = @prev_id , var0 null , @prev_var0 not null                             , @prev_var0                                , var0                                 ) char                             )  var1        , @prev_id   := id test01.x1, (select @prev_id    := null                     ,@prev_var0 := null         ) init order id, t ;  alter table test01.x2 modify var1 char(1) default null;   drop table if exists test01.x2; create table test01.x2 select * test01.x1;   update test01.x1, (select   @prev_id    := null                           , @prev_var0  := null           ) init set var0 = @prev_vr0 := if(id = @prev_id , var0 null , @prev_var0 not null                             , @prev_var0                                , var0                                 )     , @prev_id   := id  order id, t 

i interested in solution. instead create new table x2 update var0 of table x1. tried this:

update test01.x1, (select   @prev_id    := null                           , @prev_var0  := null           ) init set var0 = @prev_vr0 := if(id = @prev_id , var0 null , @prev_var0 not null                             , @prev_var0                                , var0                                 )     , @prev_id   := id                  order id, t 

but there 2 reasons why not work (and maybe others):

• order not allowed multiple table update (see link)
• @prev_id := id not work. obviously, in set statement not possible assign value directly user defined variable.

does have idea how can left table without gaps?

thanks help.

you can use stored procedures or functions:

declare stored function:

delimiter //  create function fillgap(    gapid int, verid int  ) returns varchar(255)  begin    declare gapvalue varchar(255);   -- gets value  select var0  x1   id = gapid , t <= verid , var0 not null    order t desc   limit 1    gapvalue;    return gapvalue; end // delimiter ; 

then can call in update statement:

update x1 set var0 = fillgap(id, t) var0 null 

this functions gets 1 preceeding value database, assuming t version number , id object_id.

the problem appear in case (id=2, t=2) there no preceeding value object id. in case - edit provided function , add required logics.