MySQL Update with derived tables and ORDER BY

This question is a follow up question from Link. I have a table with person (id) and one characteristic (var0) at different timepoints t. At some timepoints the characteristic is missing and I would like to fill the gaps with the former value. Here is an example of a table:

+---+---+----+            +----+---+------+------+------------------+
|id | t |var0|            | id | t | var0 | var1 | @prev_id   := id |
+---+---+----+            +----+---+------+------+------------------+
| 1 | 1 | a  |            |  1 | 1 | a    | a    |                1 |
| 1 | 3 | \N |            |  1 | 3 | \N   | a    |                1 |
| 1 | 7 | \N |            |  1 | 7 | \N   | a    |                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 is the orignal x1 table and the right table is the requested table. Here is the code to get the 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 INTO 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 AND var0 IS NULL AND @prev_var0 IS NOT NULL 
                           , @prev_var0
                               , var0 
                               ) AS CHAR
                            )  var1
       , @prev_id   := id
FROM test01.x1, (SELECT @prev_id    := NULL
                    ,@prev_var0 := NULL
        ) init
ORDER BY id, t
;

ALTER TABLE test01.x2 MODIFY var1 CHAR(1) DEFAULT NULL;


DROP TABLE IF EXISTS test01.x2;
CREATE TABLE test01.x2
SELECT * FROM test01.x1;


UPDATE test01.x1, (SELECT   @prev_id    := NULL
                          , @prev_var0  := NULL
          ) init
SET var0 = @prev_vr0 := IF(id = @prev_id AND var0 IS NULL AND @prev_var0 IS NOT NULL 
                           , @prev_var0
                               , var0 
                               )
    , @prev_id   := id

ORDER BY id, t

I would be interested in another solution. Instead to create a new table x2 I would like to update var0 of table x1. I tried this:

UPDATE test01.x1, (SELECT   @prev_id    := NULL
                          , @prev_var0  := NULL
          ) init
SET var0 = @prev_vr0 := IF(id = @prev_id AND var0 IS NULL AND @prev_var0 IS NOT NULL 
                           , @prev_var0
                               , var0 
                               )
    , @prev_id   := id                 
ORDER BY id, t

But there are two reasons why it does not work (and maybe others):

ORDER BY is not allowed with multiple table UPDATE (see Link)
@prev_id := id does not work. Obviously, in SET statement it is not possible to assign a value directly to a user defined variable.

Does anyone have an idea how I can get the left table without gaps?

Thanks for help.

Solution

You can always use stored procedures or functions:

declare a stored function:

DELIMITER //
 CREATE FUNCTION fillGap(
   gapID INT, verID INT
 ) RETURNS VARCHAR(255)
 BEGIN
   DECLARE gapValue VARCHAR(255);

 -- gets the value
 SELECT var0
 FROM x1
  WHERE id = gapID AND t <= verID AND var0 IS NOT NULL 
  ORDER BY t DESC
  LIMIT 1
 INTO
  gapValue;

  RETURN gapValue;
END //
DELIMITER ;

Then you can call it in an UPDATE statement:

UPDATE x1 SET var0 = fillGap(id, t) WHERE var0 IS NULL

this functions gets one preceeding value from a database, assuming t is a version number and id is object_id.

The problem will appear in case (id=2, t=2) as there is no preceeding value for this object id. In any case - edit the provided function and add the required logics.