So I have some tables with NULL values which I wish to fill with the last valid value. I can select a new table with the corrected Column, but I'm not sure how to update the values on the database, could someone help me with the update statement?
(select a.var_5 from wme_test as a where a.timeStamp<=b.timeStamp and a.var_5 <> 'NULL' order by timestamp desc limit 1 ) as correctedVar5
from wme_test as b
You can use a correlated update to do this.
update wme_test set var5 = (select var5 from wme_test where tm >= wme_test.tm and var5 is not null order by tm limit 1) where var5 is null
Note that the earliest timestamp in the table can't be updated with this. It would still be
null, if it was