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
