What SQLite command can replicate empty fields from a prior row?-CodePudding

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.

CodePudding user response：

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.