I maintained an in-house sqlite patch that did this for a number of years... just made the buffer a little bigger, printed something like
" set tt.<==HERE" It was quite useful for helping application users self-serve their own query problems. -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Jose Isaias Cabrera Sent: Wednesday, May 15, 2019 10:57 AM To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] SETting a value to a field with multiple conditions David Raymond, on Wednesday, May 15, 2019 10:44 AM, wrote... >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" Darn it. I hate it when you guys make me look so innocent. :-) Thanks. It would be nice if SQLite would provide a little bit more info something like, Error: ".", near "set tt.b = ": syntax error Since it knows that the problem is the ".", than just take 10 characters to the left and 10 characters to the right, and display that as the "near" part. I would have probably take the tt. out I would have seen that it had worked. :-) Thanks again for the teaching moment. josé >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 https://urldefense.proofpoint.com/v2/url?u=http-3A__mailinglists.sqlite.org_cgi-2Dbin_mailman_listinfo_sqlite-2Dusers&d=DwIGaQ&c=SFszdw3oxIkTvaP4xmzq_apLU3uL-3SxdAPNkldf__Q&r=3mFDfHOq-dU1rrQz09cmOjm2rdOZoX-v3kqQ0JKJclY&m=hY6gaH7GFW2-0ai0NLkFKjmEfga-qfozAuUjDRzYavk&s=6VXIHZsb195Ysz0f6C-vDpfunUU4ENopr0NBrOrwHH8&e= ---------------------------------------------------------------------- This message, and any attachments, is for the intended recipient(s) only, may contain information that is privileged, confidential and/or proprietary and subject to important terms and conditions available at http://www.bankofamerica.com/emaildisclaimer. If you are not the intended recipient, please delete this message. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
