In MariaDB I have this table:
OHCL
|------
|Column|Type|Null|Default
|------
|//**id**//|int(3)|No|
|index_minus|int(10)|No|0
|timestamp|int(11)|Yes|NULL
|year|int(4)|Yes|NULL
|month|int(2)|Yes|NULL
|day|int(2)|Yes|NULL
|hour|int(2)|Yes|NULL
|minute|int(2)|Yes|NULL
|close|float|Yes|NULL
|MFA_short|float|Yes|NULL
|MFA_long|float|Yes|NULL
|volume|float|Yes|NULL
|count|int(10)|Yes|NULL
== Dumping data for table OHCL
|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
I need to "shift" (or move? not sure which term best describes this) each row except id and index_minus upwards by 1, therefore discarding row with id = 1. Row 720 should be populated with default values. Default values in the last row are not that important. I will update this row with new data in a further step. I just thought, if this is at all possible, then default values would be the way to go.
The result should be looking like this:
|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
I tried this for a while now but can't find out if this is even possible?
Trying to shift just a single column for testing, the closest I got was something along the lines of this:
UPDATE OHCL SET minute = LEAD(minute) OVER (ORDER BY id) AS minute
But that didnt work.
I am new to SQL. Is this even possible with a single command?
Use LAG() window function to get the previous id of each row and join to the table to update the rows:
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;
See the demo.
If there are no gaps between the ids it's easier with a self join:
UPDATE OHCL o1
LEFT JOIN OHCL o2
ON o2.id = o1.id + 1
SET o1.timestamp = o2.timestamp,
o1.year = o2.year,
o1.month = o2.month,
o1.day = o2.day,
o1.hour = o2.hour,
o1.minute = o2.minute,
o1.close = o2.close,
o1.MFA_short = o2.MFA_short,
o1.MFA_long = o2.MFA_long,
o1.volume = o2.volume,
o1.count = o2.count;
See the demo.