Re: [sqlite] SETting a value to a field with multiple conditions

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

Re: [sqlite] SETting a value to a field with multiple conditions

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 <

Re: [sqlite] SETting a value to a field with multiple conditions

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),

Re: [sqlite] SETting a value to a field with multiple conditions

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

Re: [sqlite] SETting a value to a field with multiple conditions

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);

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

Re: [sqlite] SETting a value to a field with multiple conditions

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

[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',