I have a MySQL database temporary table with a bunch of meter readings in it against a system ID and a date (which combined make up the primary key).
Some entries have NULL for the meter reading, in which case they need to be updated to have the same meter reading as the previous day.
There is also a "diff" column which needs to have the difference between the reading for that day, and for the previous day.
I know how to update the tables using code, I am wondering if there is a good way to do it with SQL. Multiple queries is fine (I would guess that at a minimum the NULL readings would need to be updated before the diffs are calculated).
Here is an example schema with fake data, also in this sqlfiddle:
CREATE TABLE Readings (`SystemID` INT, `ReadingDate` DATE, `Reading` INT NULL, `Diff` INT NULL, PRIMARY KEY(`SystemID`,`ReadingDate`));
INSERT INTO Readings VALUES
(1, '2021-02-01', 24, NULL),
(1, '2021-02-02', 26, NULL),
(1, '2021-02-03', 23, NULL),
(1, '2021-02-04', NULL, NULL),
(1, '2021-02-05', NULL, NULL),
(1, '2021-02-06', 28, NULL),
(1, '2021-02-07', 21, NULL),
(2, '2021-02-01', 124, NULL),
(2, '2021-02-02', 126, NULL),
(2, '2021-02-03', 123, NULL),
(2, '2021-02-04', NULL, NULL),
(2, '2021-02-05', NULL, NULL),
(2, '2021-02-06', 128, NULL),
(2, '2021-02-07', 121, NULL);
After running the updates, the values would be (INSERT format kept to make it easier to see):
(1, '2021-02-01', 24, NULL),
(1, '2021-02-02', 26, 2),
(1, '2021-02-03', 23, -3),
(1, '2021-02-04', 23, 0),
(1, '2021-02-05', 23, 0),
(1, '2021-02-06', 28, 5),
(1, '2021-02-07', 21, -7),
(2, '2021-02-01', 124, NULL),
(2, '2021-02-02', 126, 2),
(2, '2021-02-03', 123, -3),
(2, '2021-02-04', 123, 0),
(2, '2021-02-05', 123, 0),
(2, '2021-02-06', 128, 5),
(2, '2021-02-07', 121, -7);
EDIT: To further complicate things, this is a temporary table in the real application.