When in doubt tinker. In this case it looks like it's the dot in "set tt.b = 'z'" that it's complaining about. Its thinking is probably "if you're updating a table you can't set a field from a different table, so no qualified field names there"
sqlite> 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); Error: near ".": syntax error sqlite> UPDATE t as tt set b = 'z' WHERE tt.a in ('p001', 'p002', 'p003', 'p004') AND tt.idate = (SELECT max(idate) from t where a = tt.a); changes: 4 total_changes: 23 sqlite> select * from t order by a, idate; n a b c d e idate ---------- ---------- ---------- ---------- ---------- ---------- ---------- 1 p001 a 1 n 4 2019-02-11 6 p001 a 4 n 4 2019-02-12 11 p001 z 3 n 4 2019-02-13 2 p002 a 1 n 4 2019-02-11 7 p002 a 5 n 4 2019-02-12 12 p002 z 4 n 4 2019-02-13 3 p003 a 2 n 4 2019-02-11 8 p003 a 6 n 4 2019-02-12 13 p003 z 5 n 4 2019-02-13 4 p004 a 2 y 4 2019-02-11 9 p004 a 7 y 4 2019-02-12 14 p004 z 6 y 4 2019-02-13 5 p005 a 3 y 4 2019-02-11 10 p005 a 8 y 4 2019-02-12 15 p005 a 7 y 4 2019-02-13 sqlite> -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Jose Isaias Cabrera Sent: Wednesday, May 15, 2019 10:23 AM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] SETting a value to a field with multiple conditions 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users