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:[email protected]] On
Behalf Of Jose Isaias Cabrera
Sent: Wednesday, May 15, 2019 10:23 AM
To: [email protected]
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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
