HSQLDB update null entries with the previous not null value

Question

How can I update the null values from all entries with the previous not null row?

For example:

create table users (
  id int primary key,
  create_date date
);

Actual entries

id  create_date
2   2018-12-03
3   NULL
4   2018-12-04
5   NULL
6   NULL

Table entries after update:

id  create_date
2   2018-12-03
3   2018-12-03
4   2018-12-04
5   2018-12-04
6   2018-12-04

Answer 1

You need to select the MAX value for CREATE_DATE from the rows that have a smaller ID than the current (updating) row. Use this max value to update the rows with NULL values. It's quite simple and doesn't need window functions.

UPDATE USERS U SET
  CREATE_DATE = (SELECT MAX(CREATE_DATE) FROM USERS USUB WHERE USUB.ID < U.ID) 
  WHERE U.CREATE_DATE IS NULL

The above statement assumes the dates are in ascending order with respect to the ID (your example). If that is not the case, you can select the CREATE_DATE for exactly the last not null value with this statement.

UPDATE USERS U SET 
 CREATE_DATE = (SELECT CREATE_DATE FROM USERS USUB WHERE USUB.ID < U.ID AND USUB.CREATE_DATE IS NOT NULL ORDER BY USUB.ID DESC LIMIT 1) 
 WHERE U.CREATE_DATE IS NULL