MySQL Update with derived tables and ORDER BY

Question

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.

Answer 1

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.

Answer 2

Thanks to Artjoman I could create a stored procedure to solve my problem. It is not so elegant as the stored function of Artjoman but it allows to pass table name and column name to the procedure. Any improvement or alternative is appreciated.

Firstly, I copied column var0 to test with two columns:

ALTER TABLE test01.x1 ADD var1 CHAR(1) DEFAULT NULL;
UPDATE test01.x1 SET var1 = var0;     

The stored procedure is:

DROP PROCEDURE IF EXISTS sp_fillGap;
DELIMITER //
CREATE PROCEDURE sp_fillGap(IN xtable VARCHAR(64)
                             , IN xvar VARCHAR(64)
                             )
BEGIN

SET @query1 = CONCAT('CREATE TABLE xt1 SELECT * FROM ',xtable,' WHERE ',xvar,' IS NOT NULL;');
SET @query2 = CONCAT('CREATE TABLE xt2 SELECT * FROM ',xtable,' WHERE ',xvar,' IS NOT NULL;');

SET @query1a = CONCAT('DROP TABLE IF EXISTS xt1;');
SET @query2a = CONCAT('DROP TABLE IF EXISTS xt2;');

SET @query3 = CONCAT('UPDATE ',xtable,' a'
                     ,' SET a.',xvar,'  = (SELECT b.',xvar
                                       ,' FROM xt1 b' 
                                   ,' WHERE a.id = b.id'
                                                -- select the last of the former cases
                                       ,'       AND b.t = (SELECT MAX(c.t)'
                                       ,'                  FROM xt2 c'
                                       ,'                  WHERE a.id = c.id'
                                       ,'                        AND c.t <= a.t'
                                       ,'                  )'
                     ,'                   );'
                     );

    PREPARE stmt1a FROM @query1a;
    EXECUTE stmt1a;

PREPARE stmt2a FROM @query2a;
EXECUTE stmt2a;

PREPARE stmt1 FROM @query1;
EXECUTE stmt1;

PREPARE stmt2 FROM @query2;
EXECUTE stmt2;

PREPARE stmt3 FROM @query3;
EXECUTE stmt3;

EXECUTE stmt1a;
EXECUTE stmt2a;

END //
DELIMITER ;

Test:

CALL sp_fillGap('test01.x1','var0');
CALL sp_fillGap('test01.x1','var1');