SQLite Forum

Help: Setting a column after finding first desired entry
Login

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é