The title does not really tells the whole story, but two versions of a software caused some discombobulation in the data, and I am trying to fix the problem. Imagine this scenario:
`create table t0 (a, b, idate, PRIMARY KEY (a,idate));
insert into t0 (a, b, idate) values ('p001', '', '2019-02-11');
insert into t0 (a, b, idate) values ('p002', '', '2019-02-11');
insert into t0 (a, b, idate) values ('p003', '', '2019-02-11');
insert into t0 (a, b, idate) values ('p004', '', '2019-02-11');
insert into t0 (a, b, idate) values ('p005', '', '2019-02-11');
insert into t0 (a, b, idate) values ('p001', 'n', '2019-02-12');
insert into t0 (a, b, idate) values ('p002', 'n', '2019-02-12');
insert into t0 (a, b, idate) values ('p003', 'y', '2019-02-12');
insert into t0 (a, b, idate) values ('p004', 'n', '2019-02-12');
insert into t0 (a, b, idate) values ('p005', 'y', '2019-02-12');
insert into t0 (a, b, idate) values ('p001', '', '2019-02-13');
insert into t0 (a, b, idate) values ('p002', '', '2019-02-13');
insert into t0 (a, b, idate) values ('p003', '', '2019-02-13');
insert into t0 (a, b, idate) values ('p004', '', '2019-02-13');
insert into t0 (a, b, idate) values ('p005', '', '2019-02-13');
insert into t0 (a, b, idate) values ('p001', '', '2019-03-14');
insert into t0 (a, b, idate) values ('p002', '', '2019-03-14');
insert into t0 (a, b, idate) values ('p003', '', '2019-03-14');
insert into t0 (a, b, idate) values ('p004', '', '2019-03-14');
insert into t0 (a, b, idate) values ('p005', '', '2019-03-14');
`
What I am trying to do is to update b with the first b value where b is not '' for the rest of the records after that first found b value != ''. So, for all idate > than the idate where the first non '' value was found. So, I thought that this would work:
`
UPDATE t0
SET b =
(
select b from t0 where b != '' GROUP BY a
)
AND a =
(
select a from t0 where b != '' GROUP BY a
)
AND idate >
(
select idate from t0 WHERE b != '' GROUP BY a
)
;
`
But this does not work. It sets all values to 0:
`
sqlite> select a, b, idate from t0 where b != '' group by a;
p001|0|2019-02-11
p002|0|2019-02-11
p003|0|2019-02-11
p004|0|2019-02-11
p005|0|2019-02-11
`
Any thoughts, help would be greatly appreciated.