By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE OHCL (
`id` INTEGER,
`index_minus` INTEGER,
`timestamp` INTEGER,
`year` INTEGER,
`month` INTEGER,
`day` INTEGER,
`hour` INTEGER,
`minute` INTEGER,
`close` FLOAT,
`MFA_short` VARCHAR(4),
`MFA_long` VARCHAR(4),
`volume` FLOAT,
`count` INTEGER
);
INSERT INTO OHCL
(`id`, `index_minus`, `timestamp`, `year`, `month`, `day`, `hour`, `minute`, `close`, `MFA_short`, `MFA_long`, `volume`, `count`)
VALUES
('1', '0', '1619743800', '2021', '4', '30', '2', '50', '0.3036', NULL, NULL, '97852.6', '41'),
('2', '1', '1619744100', '2021', '4', '30', '2', '55', '0.30358', NULL, NULL, '221278', '45'),
('3', '2', '1619744400', '2021', '4', '30', '3', '0', '0.303548', NULL, NULL, '41685', '30'),
('4', '3', '1619744700', '2021', '4', '30', '3', '5', '0.304009', NULL, NULL, '158091', '23');
Records: 4 Duplicates: 0 Warnings: 0
UPDATE OHCL o
LEFT JOIN (
SELECT *, LAG(id) OVER (ORDER BY id) prev_id
FROM OHCL
) t ON t.prev_id = o.id
SET o.timestamp = t.timestamp,
o.year = t.year,
o.month = t.month,
o.day = t.day,
o.hour = t.hour,
o.minute = t.minute,
o.close = t.close,
o.MFA_short = t.MFA_short,
o.MFA_long = t.MFA_long,
o.volume = t.volume,
o.count = t.count;
Rows matched: 4 Changed: 4 Warnings: 0
SELECT * FROM OHCL;
| id | index_minus | timestamp | year | month | day | hour | minute | close | MFA_short | MFA_long | volume | count |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 0 | 1619744100 | 2021 | 4 | 30 | 2 | 55 | 0.30358 | null | null | 221278 | 45 |
| 2 | 1 | 1619744400 | 2021 | 4 | 30 | 3 | 0 | 0.303548 | null | null | 41685 | 30 |
| 3 | 2 | 1619744700 | 2021 | 4 | 30 | 3 | 5 | 0.304009 | null | null | 158091 | 23 |
| 4 | 3 | null | null | null | null | null | null | null | null | null | null | null |