Re: [sqlite] How do I update multiple rows in a single sql statement

On Tue, 09 Dec 2014 10:46:23 -0500 Igor Tandetnik wrote: > On 12/9/2014 10:38 AM, James K. Lowden wrote: > > If the subquery to the right of the SET clause produces > > more than one row, the statement fails. > > Are you sure? Normally, a scalar subquery doesn't fail when

Re: [sqlite] How do I update multiple rows in a single sql statement

On 12/9/2014 10:38 AM, James K. Lowden wrote: If the subquery to the right of the SET clause produces more than one row, the statement fails. Are you sure? Normally, a scalar subquery doesn't fail when the resultset contains more than one row - it just silently produces the value from the

Re: [sqlite] How do I update multiple rows in a single sql statement

On Mon, 08 Dec 2014 20:57:00 -0500 Igor Tandetnik wrote: > Yes, there are workarounds (a view; or REPLACE INTO may sometimes be > pressed into service). But I, for one, kinda miss UPDATE ... FROM. Be careful what you wish for. :-) The only implementation of

Re: [sqlite] How do I update multiple rows in a single sql statement

On 9-12-2014 02:31, Igor Tandetnik wrote: On 12/8/2014 8:20 PM, Keith Medcalf wrote: update temp_table set id=(select id from some_table where c=42), operation='UPDATE' where exists (select 1 from some_table s where s.a=temp_table.a and s.b=temp_table.b and s.c=42); is the proper

Re: [sqlite] How do I update multiple rows in a single sql statement

On 12/8/2014 8:43 PM, Keith Medcalf wrote: That is not a problem -- and I only fixed the where clause, not the set clause. The data will be "hot" on the second access, so the overhead of the additional access is negligible since CPU usage is negligible (even if it goes through all the motions

Re: [sqlite] How do I update multiple rows in a single sql statement

ite-users- >boun...@sqlite.org] On Behalf Of Igor Tandetnik >Sent: Monday, 8 December, 2014 18:32 >To: sqlite-users@sqlite.org >Subject: Re: [sqlite] How do I update multiple rows in a single sql >statement > >On 12/8/2014 8:20 PM, Keith Medcalf wrote: >> >> update temp_t

Re: [sqlite] How do I update multiple rows in a single sql statement

On 12/8/2014 8:20 PM, Keith Medcalf wrote: update temp_table set id=(select id from some_table where c=42), operation='UPDATE' where exists (select 1 from some_table s where s.a=temp_table.a and s.b=temp_table.b and s.c=42); is the proper way of phrasing of a correlated subquery

Re: [sqlite] How do I update multiple rows in a single sql statement

Sent: Monday, 8 December, 2014 13:36 >To: sqlite-users@sqlite.org >Subject: Re: [sqlite] How do I update multiple rows in a single sql >statement > >On 8-12-2014 21:17, Igor Tandetnik wrote: >> On 12/8/2014 3:08 PM, Luuk wrote: >>> i hope this does it: >>&g

Re: [sqlite] How do I update multiple rows in a single sql statement

On 12/8/2014 3:35 PM, Luuk wrote: update temp_table set id=(select id from some_table where c=42), operation='UPDATE' where exists (select 1 from some_table s, temp_table t where s.a=t.a and s.b=t.b and s.c=42); Same thing. Your WHERE clause doesn't depend on the values in the

Re: [sqlite] How do I update multiple rows in a single sql statement

On 8-12-2014 21:17, Igor Tandetnik wrote: On 12/8/2014 3:08 PM, Luuk wrote: i hope this does it: update temp_table set id=(select id from some_table where c=42), operation='UPDATE' where exists (select 1 from some_table s, temp_table t where s.a=t.a and s.b=t.b); This updates all

Re: [sqlite] How do I update multiple rows in a single sql statement

The visibility of table created in with clause is only in the SET part. The following is a valid sql statement in sqlite3. with ds as (select id, a , b, c from some_table where c = 43) update temp_table set id = (select ds.id from ds where ds.a = temp_table.a AND ds.b = temp_table.b),

Re: [sqlite] How do I update multiple rows in a single sql statement

On 12/8/2014 3:08 PM, Luuk wrote: i hope this does it: update temp_table set id=(select id from some_table where c=42), operation='UPDATE' where exists (select 1 from some_table s, temp_table t where s.a=t.a and s.b=t.b); This updates all rows in temp_table, as long as at least one

Re: [sqlite] How do I update multiple rows in a single sql statement

On 8-12-2014 20:50, Venkat Murty wrote: How do I update multiple rows in a single sql statement. Two tables: create table some_table(id, a, b, c); create table temp_table (id, operation, a, b, c); Operation: Updating id, operation fields in temp_table if the record exists in some_table.

[sqlite] How do I update multiple rows in a single sql statement

How do I update multiple rows in a single sql statement. Two tables: create table some_table(id, a, b, c); create table temp_table (id, operation, a, b, c); Operation: Updating id, operation fields in temp_table if the record exists in some_table. with ds as (select id, a , b, c from