Page History

SET SQL_SAFE_UPDATES=0;
update full_tableName tbl
 set flag=flag | 1
 where tbl.effectiveTime=(select max(sub.effectiveTime) from copy_full_tableName sub where sub.id=tbl.id and sub.effectiveTime<='20190731');
update full_tableName tbl
 set flag=flag | 2
 where tbl.effectiveTime=(select max(sub.effectiveTime) from copy_full_tableName sub where sub.id=tbl.id and sub.effectiveTime<='20190131');
update full_tableName tbl
 set flag=flag | 4
 where tbl.effectiveTime=(select max(sub.effectiveTime) from copy_full_tableName sub where sub.id=tbl.id and sub.effectiveTime<='20180731');
update full_tableName tbl
 set flag=flag | 8
 where tbl.effectiveTime=(select max(sub.effectiveTime) from copy_full_tableName sub where sub.id=tbl.id and sub.effectiveTime<='20180131');
SET SQL_SAFE_UPDATES=1;

-- Create temporary table for the supersededTime values
CREATE TEMPORARY TABLE tmp (id CHAR(36) NOT NULL,effectiveTime DATETIME,supersededTime DATETIME, PRIMARY KEY (id,effectiveTime));

-- Compute the supersededTime values for each combination of id+effectiveTime and add these to the temporary file
INSERT INTO tmp SELECT tbl.id, tbl.effectiveTime, (SELECT IFNULL(MIN(sub.effectiveTime),DATE "99991231") FROM full_tableName sub
    WHERE tbl.id=sub.id AND tbl.effectiveTime<sub.effectiveTime) supersededTime FROM full_tableName tbl;

-- Apply the appropriate supersededTime values to each row in the full table
UPDATE full_tableName tbl
    JOIN tmp
    SET tbl.supersededTime=tmp.supersededTime
        WHERE tmp.id=tbl.id AND tmp.effectiveTime=tbl.effectiveTime;