Table-valued syntax is your friend:
-- SQLite version 3.27.2 [ Release: 2019-02-25 ] on SQLitespeed
version 2.1.2.47.
--
================================================================================================
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')
,('p002', 'a', 1, 'n', 4, '2019-02-11')
,('p003', 'a', 2, 'n', 4, '2019-02-11')
,('p004', 'a', 2, 'y', 4, '2019-02-11')
,('p005', 'a', 3, 'y', 4, '2019-02-11')
,('p001', 'a', 4, 'n', 4, '2019-02-12')
,('p002', 'a', 5, 'n', 4, '2019-02-12')
,('p003', 'a', 6, 'n', 4, '2019-02-12')
,('p004', 'a', 7, 'y', 4, '2019-02-12')
,('p005', 'a', 8, 'y', 4, '2019-02-12')
,('p001', 'a', 3, 'n', 4, '2019-02-13')
,('p002', 'a', 4, 'n', 4, '2019-02-13')
,('p003', 'a', 5, 'n', 4, '2019-02-13')
,('p004', 'a', 6, 'y', 4, '2019-02-13')
,('p005', 'a', 7, 'y', 4, '2019-02-13')
;
UPDATE t AS tx SET b = 'z' WHERE (a,idate) = (SELECT ty.a,MAX(ty.idate)
FROM t AS ty WHERE ty.a = tx.a GROUP BY ty.a);
select * from t;
-- n | a | b | c | d | e | idate
-- ------------ | ---- | --- | --- | --- | --- | ----------
-- 1 | p001 | a | 1 | n | 4 | 2019-02-11
-- 2 | p002 | a | 1 | n | 4 | 2019-02-11
-- 3 | p003 | a | 2 | n | 4 | 2019-02-11
-- 4 | p004 | a | 2 | y | 4 | 2019-02-11
-- 5 | p005 | a | 3 | y | 4 | 2019-02-11
-- 6 | p001 | a | 4 | n | 4 | 2019-02-12
-- 7 | p002 | a | 5 | n | 4 | 2019-02-12
-- 8 | p003 | a | 6 | n | 4 | 2019-02-12
-- 9 | p004 | a | 7 | y | 4 | 2019-02-12
-- 10 | p005 | a | 8 | y | 4 | 2019-02-12
-- 11 | p001 | z | 3 | n | 4 | 2019-02-13
-- 12 | p002 | z | 4 | n | 4 | 2019-02-13
-- 13 | p003 | z | 5 | n | 4 | 2019-02-13
-- 14 | p004 | z | 6 | y | 4 | 2019-02-13
-- 15 | p005 | z | 7 | y | 4 | 2019-02-13
-- Item Stats: Item No: 4 Query Size (Chars): 18
-- Result Columns: 7 Result Rows: 15
-- VM Work Steps: 141 Rows Modified: 0
-- Full Query Time: 0d 00h 00m and 00.003s
-- Query Result: Success.
--
------------------------------------------------------------------------------------------------
Cheers,
Ryan
On 2019/05/15 4:22 PM, Jose Isaias Cabrera wrote:
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
