heczaco heczaco - 4 months ago 9
SQL Question

SQLite filling null values

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 b.timeStamp,
b.var_5,
(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


the output for this query is this

enter image description here

vkp vkp
Answer

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

Sample Fiddle

Note that the earliest timestamp in the table can't be updated with this. It would still be null, if it was null previously.