SETting a value to a field with multiple conditions

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

SETting a value to a field with multiple conditions

Jose Isaias Cabrera-4

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SETting a value to a field with multiple conditions

David Raymond
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:[hidden email]] On Behalf Of Jose Isaias Cabrera
Sent: Wednesday, May 15, 2019 10:23 AM
To: [hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SETting a value to a field with multiple conditions

Jose Isaias Cabrera-4

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:[hidden email]] On Behalf Of Jose Isaias Cabrera
>Sent: Wednesday, May 15, 2019 10:23 AM
>To: [hidden email]
>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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SETting a value to a field with multiple conditions

R Smith-2
In reply to this post by Jose Isaias Cabrera-4
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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SETting a value to a field with multiple conditions

Nelson, Erik - 2
In reply to this post by Jose Isaias Cabrera-4
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:[hidden email]] On Behalf Of Jose Isaias Cabrera
Sent: Wednesday, May 15, 2019 10:57 AM
To: SQLite mailing list <[hidden email]>
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:[hidden email]] On Behalf Of Jose Isaias Cabrera
>Sent: Wednesday, May 15, 2019 10:23 AM
>To: [hidden email]
>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
[hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SETting a value to a field with multiple conditions

R Smith-2
In reply to this post by Jose Isaias Cabrera-4
I of course forgot to remove 'p005' from the list (luckily David
didn't!), so the query should have been:

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) AND a < 'p005';

which works well here (picking the minimal route), but your actual query
may or may not work better with the IN ('p001', 'p002',...) method of
checking.


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//...
>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SETting a value to a field with multiple conditions

Jose Isaias Cabrera-4

R Smith, on Wednesday, May 15, 2019 11:06 AM, wrote...
>I of course forgot to remove 'p005' from the list (luckily David
>didn't!), so the query should have been:
>
>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) AND a < 'p005';
>
>which works well here (picking the minimal route), but your actual query
>may or may not work better with the IN ('p001', 'p002',...) method of
>checking.

This is great, though.  Because sometimes I do have to use a BETWEEN 'p002' AND 'p005'. In this instance, on the real problem, I could not do it because the select would not include a sequential list. But, it will be use.  Thanks.

josé
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: SETting a value to a field with multiple conditions

Jose Isaias Cabrera-4
In reply to this post by Nelson, Erik - 2

Nelson, Erik - 2, on Wednesday, May 15, 2019 11:04 AM, wrote...
>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.

Yes. I would think that just a simple,

error: ".", near " set tt.d = "

where the near constains 10 characters to the left of the error, and 10 character to the right of the error, including the error.  Or something like that would not add too much to the size and buffer of sqlite.  But, then again, I am just a newbie.  Which is probably why I am asking for it. :-)  Thanks.

josé


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users