Help: Setting a column after finding first desired entry
(1) By jose isaias cabrera (jicman) on 2020-05-08 18:36:10 [source]
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.
(2) By Keith Medcalf (kmedcalf) on 2020-05-08 19:36:53 in reply to 1 [link] [source]
Do you mean something like this:
update t0
set b = coalesce((select b
from t0 as s
where a == t0.a
and idate < t0.idate
and b != ''
order by idate desc
limit 1), b)
where b == ''
;
(3) By jose isaias cabrera (jicman) on 2020-05-08 21:04:18 in reply to 2 [link] [source]
Do you mean something like this:
You are so good. Thanks. Yep, exactly that.
josé