Re: [sqlite] Row values with IN

Simon Slavin wrote: > I think that the documentation doesn't sufficiently explain the problem. > Or maybe the use of IN or lists deserves its own page rather than being > buried in the extremely long page on expressions. says: | For a row-value IN operator,

Re: [sqlite] Row values with IN

On 23-4-2019 18:08, Simon Slavin wrote: On 23 Apr 2019, at 4:14pm, Charles Leifer wrote: SELECT * FROM info WHERE (year,month,day) IN ((2019, 1, 1), (2019, 2, 1)); Perhaps the row-value doc could clarify the behavior of IN with row values? This ((2019, 1, 1), (2019, 2, 1)) This works:

Re: [sqlite] Row values with IN

On 23 Apr 2019, at 4:14pm, Charles Leifer wrote: > SELECT * FROM info WHERE (year,month,day) IN ((2019, 1, 1), (2019, 2, 1)); > > Perhaps the row-value doc could clarify the behavior of IN with row values? This > ((2019, 1, 1), (2019, 2, 1)) is not a list of scalers, it's a list of lists of

Re: [sqlite] Row values with IN

Thanks, makes sense. I think what confused me is that in the doc for row values, it states: Two row values of the same size can be compared using operators <, <=, >, >=, =, <>, IS, IS NOT, IN, NOT IN, BETWEEN, or CASE. The row value doc example shows how to use BETWEEN, for instance: SELECT *

Re: [sqlite] Row values with IN

On 23 Apr 2019, at 3:52pm, Charles Leifer wrote: > My question, though, is why is the VALUES bit needed for the WHERE clause in > this case? " If the right operand of an IN or NOT IN operator is a list of values, each of those values must be scalars

Re: [sqlite] Row values with IN

This is a question about SQL language support and the handling of row-values, and I thought my question was quite clear. I appreciate your offers to help, but they do not have anything to do with the question I am asking. As I wrote: My question, though, is why is the VALUES bit needed for the

Re: [sqlite] Row values with IN

On 23 Apr 2019, at 3:42pm, Charles Leifer wrote: > Thanks Simon -- this is just a minimal example. The WHERE clause is needed > because, unless I would provide a CASE WHEN for every single (key, value), > then the UPDATE would set the "extra" value of any unmatched key, value to > NULL. You

Re: [sqlite] Row values with IN

Thanks Simon -- this is just a minimal example. The WHERE clause is needed because, unless I would provide a CASE WHEN for every single (key, value), then the UPDATE would set the "extra" value of any unmatched key, value to NULL. Please remember this is just an example. I'd like to generalize

Re: [sqlite] Row values with IN

On 23 Apr 2019, at 3:11pm, Charles Leifer wrote: > UPDATE "bu" SET "extra" = CASE ("key", "value") > WHEN ('k1', 1) THEN 100 > WHEN ('k2', 2) THEN -200 > WHEN ('k3', 3) THEN 30 > END > WHERE ("key", "value") IN (('k1', 1), ('k2', 2), ('k3', 3)); Your WHERE clause it not needed, and SQLite is

[sqlite] Row values with IN

Let's say I have a simple table with a composite primary key (key, value) and an extra field: CREATE TABLE IF NOT EXISTS "bu" ( "key" TEXT NOT NULL, "value" INTEGER NOT NULL, "extra" INTEGER NOT NULL, PRIMARY KEY ("key", "value")); I'll put 3 rows in the table: INSERT INTO "bu" ("key",

Re: [sqlite] row values with BETWEEN

On 11/23/16, Mark Brand wrote: > Hi, > > i hope I'm not overlooking something obvious, but the last result below > is not what I would expect. https://www.sqlite.org/src/info/1a68465637e1b014 -- D. Richard Hipp d...@sqlite.org ___

[sqlite] row values with BETWEEN

Hi, i hope I'm not overlooking something obvious, but the last result below is not what I would expect. Just wondering if this could somehow be correct and, if so, how to understand it. regards, Mark $ sqlite3 SQLite version 3.15.1 2016-11-04 12:08:49 Enter ".help" for usage hints.