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 the > resultset contains more than one row - it just silently produces the > value from the first row of the first column. Well, I *was* sure. I don't know about "normally", but you're right that SQLite gets it wrong, see below. I'm pretty sure the standard calls for a diagnostic anywhere a scalar is required and not provided. There is a workaround worth knowing: if you add, group by k having count(*) = 1 to the UPDATE statement below, it works correctly in the sense that it becomes deterministic. A separate check is required of course to determine if there were any count(*) > 1. [snip] create table T ( k int primary key, v string ); create table S ( k int, v string, primary key( k,v) ); insert into T values (1, 'a'), (2, 'b'); insert into S values (1, 'y'), (1, 'z'); select * from T; k v -- -- 1 a 2 b select * from S; k v -- -- 1 y 1 z select * from T join S on T.k = S.k; k v k v -- -- -- -- 1 a 1 y 1 a 1 z update T set v = (select v from S where k = T.k) where exists ( select 1 from S where k = T.k ); select * from T; k v -- -- 1 y 2 b [pins] --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 first row of the first column. I'm pretty sure that's how it works in SQLite (but am too lazy to check). With SQL Server's syntax, it succeeds with the target holding the "last" value, whatever that was. Succeeding with the target holding the "first" value doesn't sound like a significant difference. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 UPDATE...FROM that I know is on SQL Server (Sybase & Microsoft). If the join criteria are sastified by more than one row, each successive value is applied. The result is nondeterministic, produces no diagnostic, and cannot be prevented. The standard SQL syntax -- while verbose, granted -- at least gets the right answer. If the subquery to the right of the SET clause produces more than one row, the statement fails. With SQL Server's syntax, it succeeds with the target holding the "last" value, whatever that was. --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 way of phrasing of a correlated subquery ... Now the problem is that (select id from some_table where c=42) takes an id from some row of some_table - not necessarily the row with matching a and b. OK, thanks for the extra input... I hope it's enough for the OP. Without some form of UPDATE...FROM (supported by some SQL engines, but not SQLite), I can't think of a way to avoid repeating the whole three-conjuncts condition twice - once in SET id=, and again in WHERE. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 of cold access) compared to I/O usage -- given a large enough page cache in RAM (so no I/O is required) to hold the pages involved in the tree traversals, of course. It's not really about efficiency - it's about verbosity. Imagine that you need to update not one but 10 fields in temp_table from the corresponding row in some_table: now you need to repeat the same condition 11 times. Imagine further that the condition is more complicated, involving joins on several tables. Pretty soon, we are talking one really long and convoluted query. Yes, there are workarounds (a view; or REPLACE INTO may sometimes be pressed into service). But I, for one, kinda miss UPDATE ... FROM. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How do I update multiple rows in a single sql statement
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 of cold access) compared to I/O usage -- given a large enough page cache in RAM (so no I/O is required) to hold the pages involved in the tree traversals, of course. It is also possible to construct a view which may be used with an instead-of trigger to achieve indirectly the exact behaviour implemented by executing an update query of the form: UPDATE a SET x=b.b FROM a, b WHERE a.a = b.a AND ... You simply need to create a view which outputs the rowid's needing updating, and the values that should be updated. Then the instead of update trigger on the view merely applies the updates to the underlying real table. --- Theory is when you know everything but nothing works. Practice is when everything works but no one knows why. Sometimes theory and practice are combined: nothing works and no one knows why. >-Original Message- >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-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_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 ... > >Now the problem is that (select id from some_table where c=42) takes an >id from some row of some_table - not necessarily the row with matching a >and b. > >Without some form of UPDATE...FROM (supported by some SQL engines, but >not SQLite), I can't think of a way to avoid repeating the whole >three-conjuncts condition twice - once in SET id=, and again in WHERE. >-- >Igor Tandetnik > >___ >sqlite-users mailing list >sqlite-users@sqlite.org >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 ... Now the problem is that (select id from some_table where c=42) takes an id from some row of some_table - not necessarily the row with matching a and b. Without some form of UPDATE...FROM (supported by some SQL engines, but not SQLite), I can't think of a way to avoid repeating the whole three-conjuncts condition twice - once in SET id=, and again in WHERE. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How do I update multiple rows in a single sql statement
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 ... --- Theory is when you know everything but nothing works. Practice is when everything works but no one knows why. Sometimes theory and practice are combined: nothing works and no one knows why. >-Original Message- >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >boun...@sqlite.org] On Behalf Of Luuk >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: >>> >>> 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 row in >> temp_table matches one row in some_table. In other words, it updates no >> rows, or all rows - never just some. > >you are right > >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); >___ >sqlite-users mailing list >sqlite-users@sqlite.org >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 current row of temp_table - it's either always true, or always false. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 rows in temp_table, as long as at least one row in temp_table matches one row in some_table. In other words, it updates no rows, or all rows - never just some. you are right 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); ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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), operation = 'UPDATE'; Have to take care using case statement when a match is not found. Fiberlink Disclaimer: The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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 row in temp_table matches one row in some_table. In other words, it updates no rows, or all rows - never just some. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
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. with ds as (select id, a , b, c from some_table where c = 42) update temp_table set id = ds.id, operation = 'UPDATE' WHERE ds.a = temp_table.a AND ds.b = temp_table.b; I get the error " no such column: ds.id" 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); ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
