Hi. I know this has been probably asked before by someone, but imagine the following scenario:
create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate); insert into t (a, b, c, d, e, idate) values ('p001', 'a', 1, 'n', 4, '2019-02-11'); insert into t (a, b, c, d, e, idate) values ('p002', 'a', 1, 'n', 4, '2019-02-11'); insert into t (a, b, c, d, e, idate) values ('p003', 'a', 2, 'n', 4, '2019-02-11'); insert into t (a, b, c, d, e, idate) values ('p004', 'a', 2, 'y', 4, '2019-02-11'); insert into t (a, b, c, d, e, idate) values ('p005', 'a', 3, 'y', 4, '2019-02-11'); insert into t (a, b, c, d, e, idate) values ('p001', 'a', 4, 'n', 4, '2019-02-12'); insert into t (a, b, c, d, e, idate) values ('p002', 'a', 5, 'n', 4, '2019-02-12'); insert into t (a, b, c, d, e, idate) values ('p003', 'a', 6, 'n', 4, '2019-02-12'); insert into t (a, b, c, d, e, idate) values ('p004', 'a', 7, 'y', 4, '2019-02-12'); insert into t (a, b, c, d, e, idate) values ('p005', 'a', 8, 'y', 4, '2019-02-12'); insert into t (a, b, c, d, e, idate) values ('p001', 'a', 3, 'n', 4, '2019-02-13'); insert into t (a, b, c, d, e, idate) values ('p002', 'a', 4, 'n', 4, '2019-02-13'); insert into t (a, b, c, d, e, idate) values ('p003', 'a', 5, 'n', 4, '2019-02-13'); insert into t (a, b, c, d, e, idate) values ('p004', 'a', 6, 'y', 4, '2019-02-13'); insert into t (a, b, c, d, e, idate) values ('p005', 'a', 7, 'y', 4, '2019-02-13'); select * from t; I would like to change field b to 'z' for all records in ('p001', 'p002', 'p003', 'p004') but to the latest idate. I know I can do multiple single statements, ie, UPDATE t set b = 'z' WHERE a = 'p001' AND idate = (SELECT max(idate) FROM t WHERE a = 'p001'); ... UPDATE t set b = 'z' WHERE a = 'p004' AND idate = (SELECT max(idate) FROM t WHERE a = 'p004'); but is there a much nicer way of doing it in one call? I was thinking something like, UPDATE t SET b = 'z' WHERE a IN ('p001', 'p002', 'p003', 'p004') AND idate = (SELECT max(idate) WHERE a = ?); I don't know how to do the last part. I was trying things like, UPDATE t as tt set tt.b = 'z' WHERE tt.a in ('p001', 'p002', 'p003', 'p004') AND tt.idate = (SELECT max(idate) from t where a = tt.a); This one gives errors out with, Error: near ".": syntax error It would be nice to know which . is the problem. :-) Any thoughts? Thanks. josé _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users