[FIXED] What SQLite command can replicate empty fields from a prior row?

Issue

I am importing some data into sqlite, where some columns use an empty value to indicate that the value is the same as the previous row's value.

How can I accomplish this purely in SQLite SQL? I have a row number that indicates ordering.

For example, after importing and processing with-blank, it should appear to be identical to importing non-blank.

$ cat with-blank.csv 
n,x,y
1,aaa,foo
2,,bar
3,,baz
4,bbb,able
5,,baker

cat non-blank.csv
n,x,y
1,aaa,foo
2,aaa,bar
3,aaa,baz
4,bbb,able
5,bbb,baker

sqlite> .mode csv
sqlite> .import with-blank.csv t

Note: Not a duplicate of Fill empty cells in excel sheet (or SQLite) with the value of nearest filled cell above, which includes Excel as a potential solution.

I'm looking for the Pure SQL command which can do this. I can of course do this as a preprocessing step prior to importing into SQLite, but I would like to learn what commands, if any, can do this in SQLite.

Solution

After you have imported the table, you can update the empty column values by using a correlated subquery:

UPDATE tablename AS t1
SET x = (
  SELECT t2.x 
  FROM tablename t2 
  WHERE t2.n < t1.n AND COALESCE(t2.x, '') <> '' 
  ORDER BY t2.n DESC LIMIT 1
)
WHERE COALESCE(t1.x, '') = '';

See the demo.

Answered By - forpas

Answer Checked By - David Marino (PHPFixing Volunteer)