Replace null values with the latest value in a column using MySQL or H2

Question

The same question was asked, however all the answers that were provided were meant for the SQL server 2008 and neither of the approaches worked for MySQL or H2:

replace NULL values with latest non-NULL value in resultset series (SQL Server 2008 R2)

Similar question (also SQL server 2008 and we don't know all tables)

Replace null value by latest value

What I need is something that would work with either MySQL or H2

So if we have

product timestamp          price 
------- ----------------   -----
   5678 2008-01-01         12.34
   5678 2008-01-02         NULL
   5678 2008-01-03         NULL
   5678 2008-01-03         23.45
   5678 2008-01-04         NULL

The result should be

product timestamp          price 
------- ----------------   -----
   5678 2008-01-01         12.34
   5678 2008-01-02         12.34
   5678 2008-01-03         12.34
   5678 2008-01-03         23.45
   5678 2008-01-04         23.45

MySQL code:

CREATE TABLE `table1` (
  `product` int(11) NOT NULL,
  `timestamp` date NOT NULL,
  `price` decimal(10,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



INSERT INTO `table1` (`product`, `timestamp`, `price`) VALUES
(5678, '2008-01-01', '12'),
(5678, '2008-01-02', NULL),
(5678, '2008-01-03', NULL),
(5678, '2008-01-03', '23'),
(5678, '2008-01-04', NULL);

Please keep it simple.

@ysth I have updated a question with the SQL code to create the table — 3xCh1_23, Aug 10 '20 at 22:15
yes, I see your sample data doesn't have one; does your real use case really not have a primary key? — ysth, Aug 10 '20 at 22:16
Please explain how the pk is relevant to a final solution and provide an answer using the pk — 3xCh1_23, Aug 10 '20 at 22:16
it's just kind of bizarre not to have one (in fact, mysql forces you to have one and makes a hidden one for you if you don't, so you don't save anything by not having one) — ysth, Aug 10 '20 at 22:18
if you have two timestamps the same, you can specify that one with a lesser pk is treated as earlier; otherwise this becomes an ambiguous problem — ysth, Aug 10 '20 at 22:21
do you want the price for the latest earlier record with any product, or with the same product? — ysth, Aug 10 '20 at 23:00

score 2 · Accepted Answer · answered Aug 10 '20 at 22:18

If you have mysql 6.x you can use user defined variable

CREATE TABLE table1 (
  `product` INTEGER,
  `timestamp` DATETIME,
  `price` VARCHAR(5)
);

INSERT INTO table1
  (`product`, `timestamp`, `price`)
VALUES
  ('5678', '2008-01-01 12:00', '12.34'),
  ('5678', '2008-01-01 12:01', NULL),
  ('5678', '2008-01-01 12:02', NULL),
  ('5678', '2008-01-01 12:03', '23.45'),
  ('5678', '2008-01-01 12:04', NULL);

SELECT 
`product`
, `timestamp`
, @price := IF(`price` IS NULL, @price,`price`) 'price'
FROM (SELECT * FROM table1 ORDER BY `timestamp`) t1,(SELECT @price := 0) t2

product | timestamp           | price
------: | :------------------ | :----
   5678 | 2008-01-01 12:00:00 | 12.34
   5678 | 2008-01-01 12:01:00 | 12.34
   5678 | 2008-01-01 12:02:00 | 12.34
   5678 | 2008-01-01 12:03:00 | 23.45
   5678 | 2008-01-01 12:04:00 | 23.45

db<>fiddle here

pretty sure the inclusion of product means update with the last price for that product — ysth, Aug 10 '20 at 22:23

ysth · Answer 2 · 2020-08-10T22:59:05.287

I understood this problem as being to update the table data, and that one product's prices should not affect another product's prices. That looks like this:

set @price:=null, @product:=null;
update table1
    set price=if(price is not null,
        @price:=price,
        if(product=@product,@price,price)
    ),
    product=@product:=product
order by product, timestamp;

If the goal is just to replace null values with the previous value during select, this would be easily accomplished using window functions, except that neither mysql nor mariadb has yet implemented the LAG() IGNORE NULLS function :( so it requires use of variables (see nbk's answer) or a self-join:

select t.product, t.timestamp, coalesce(t.price, substr(max(concat(t2.timestamp,t2.price)),length(t.timestamp)+1)) price
from table1 t
left join table1 t2 on t2.product=t.product and t2.timestamp < t.timestamp and t.price is null and t2.price is not null
group by t.product, t.timestamp, t.price;

The group by is needed but will remove duplicate entries; grouping by a primary key would be better.

Upvoted because I wasn't clear on details saying that table cannot be updated, ran the code in MySQL and it worked. — 3xCh1_23, Aug 10 '20 at 22:43

Replace null values with the latest value in a column using MySQL or H2

2 Answers2