Re: [sqlite] Update SQLITE

On Sunday, 5 January, 2020 16:39, gideo...@lutzvillevineyards.com wrote: >I have the following SQLITE query : > >SELECT BlokkeklaarAliasnaam, BlokkeklaarKultivar, sum(BlokkeklaarSkatting) >FROM Blokkeklaar >GROUP BY BlokkeklaarAliasnaam, BlokkeklaarKultivar; >

[sqlite] Update SQLITE

I have the following SQLITE query : SELECT BlokkeklaarAliasnaam, BlokkeklaarKultivar, sum(BlokkeklaarSkatting) FROM Blokkeklaar GROUP BY BlokkeklaarAliasnaam, BlokkeklaarKultivar; I cannot f

Re: [sqlite] UPDATE SET using column-name-list Syntax error

Richard Hipp wrote: On 12/10/17, javaj1...@elxala.com wrote: According to the documentation UPDATE SET admits column-name-list as argument but I cannot get it working. Here some tries DROP TABLE IF EXISTS test; CREATE TABLE test(a,b,c); -- ok UPDATE test SET a = "vala", b = "valb" ; UPDATE te

Re: [sqlite] UPDATE SET using column-name-list Syntax error

>UPDATE test SET (a, b) = "vala", "valb" ; Should that not be (a, b) = (‘vala’, ‘valb’); >UPDATE test SET (a, b) = (SELECT "vala", "valb") ; Should that not be (a, b) = ((SELECT ‘vala’), ‘valb’); ___ sqlite-users mailing list sqlite-users@mailinglis

Re: [sqlite] UPDATE SET using column-name-list Syntax error

On 12/10/17, javaj1...@elxala.com wrote: > > According to the documentation UPDATE SET admits column-name-list as > argument > but I cannot get it working. Here some tries > > DROP TABLE IF EXISTS test; CREATE TABLE test(a,b,c); > > -- ok > UPDATE test SET a = "vala", b = "valb" ; > UPDATE test SE

[sqlite] UPDATE SET using column-name-list Syntax error

Hi, According to the documentation UPDATE SET admits column-name-list as argument but I cannot get it working. Here some tries DROP TABLE IF EXISTS test; CREATE TABLE test(a,b,c); -- ok UPDATE test SET a = "vala", b = "valb" ; UPDATE test SET a = (SELECT "vala"), b = (SELECT "valb") ; -- not

Re: [sqlite] Update the SQLite package in Tcl

On Sunday, 19 November, 2017 20:46, Joseph R. Justice wrote: >On Sun, Nov 19, 2017 at 4:49 PM, Keith Medcalf >wrote: >> On 19 November, 2017 10:50, Joseph R. Justice >asked: >> The same applies for SQLite and anything else that has a stable >> interface. >Fair enough, but then we're back

Re: [sqlite] Update the SQLite package in Tcl

On Sat, Nov 18, 2017 at 1:06 PM, Balaji Ramanathan < balaji.ramanat...@gmail.com> wrote: I have installed Tcl/Tk from a couple of places on the web (activetcl > and magicsplat), and I find that neither of them has the latest version of > sqlite3 as the standard sqlite3 package. ActiveTcl seem

Re: [sqlite] Update the SQLite package in Tcl

On Sun, Nov 19, 2017 at 4:49 PM, Keith Medcalf wrote: > On 19 November, 2017 10:50, Joseph R. Justice asked: > > >I'd think that dropping a newer version of SQLite, compiled as a run- > >time linkable library, into a pre-existing binary compiled to use a > >previous version of SQLite would req

Re: [sqlite] Update the SQLite package in Tcl

On 19 November, 2017 10:50, Joseph R. Justice asked: >I'd think that dropping a newer version of SQLite, compiled as a run- >time linkable library, into a pre-existing binary compiled to use a >previous version of SQLite would require the ABI for SQLite (compiled >for use >as a linkable library

Re: [sqlite] Update the SQLite package in Tcl

On Nov 18, 2017 1:22 PM, "Richard Hipp" wrote: On 11/18/17, Balaji Ramanathan wrote: > Hi, > > I have installed Tcl/Tk from a couple of places on the web (activetcl > and magicsplat), and I find that neither of them has the latest version of > sqlite3 as the standard sqlite3 package. Acti

Re: [sqlite] Update the SQLite package in Tcl

Are there equivalent instructions for Windows? Thank you. Balaji Ramanathan -- Forwarded message -- From: Richard Hipp To: SQLite mailing list Cc: Bcc: Date: Sat, 18 Nov 2017 13:22:45 -0500 Subject: Re: [sqlite] Update the SQLite package in Tcl On 11/18/17, Balaji Ramanathan

Re: [sqlite] Update the SQLite package in Tcl

On 11/18/17, Balaji Ramanathan wrote: > Hi, > > I have installed Tcl/Tk from a couple of places on the web (activetcl > and magicsplat), and I find that neither of them has the latest version of > sqlite3 as the standard sqlite3 package. ActiveTcl seems to be linked to > sqlite 3.13 while mag

[sqlite] Update the SQLite package in Tcl

Hi, I have installed Tcl/Tk from a couple of places on the web (activetcl and magicsplat), and I find that neither of them has the latest version of sqlite3 as the standard sqlite3 package. ActiveTcl seems to be linked to sqlite 3.13 while magicsplat's version comes with sqlite 3.20. Wha

Re: [sqlite] SQLite Update With CTE

2:25 To: SQLite Users Subject: [sqlite] SQLite Update With CTE Hi I am rewriting an old Perl script that selectively updates data from one table to another using this statement: UPDATE CUSTOMERS SET cust1= ?, cust2= NULL, street = ?, town = ?, postcode = ?

Re: [sqlite] SQLite Update With CTE

lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of John McMahon >Sent: Monday, 21 August, 2017 22:25 >To: SQLite Users >Subject: [sqlite] SQLite Update With CTE > >Hi

Re: [sqlite] SQLite Update With CTE

John McMahon wrote: > should be > UPDATE CUSTOMERS -- remove 'as c' > SET > cust1= (select customer from test where custnum = CUSTOMERS.custnum), > WHERE custnum IN (SELECT custnum FROM test) Yes. Regards, Clemens ___ sqlite-users mailing list

Re: [sqlite] SQLite Update With CTE

On 22/08/2017 16:41, Clemens Ladisch wrote: John McMahon wrote: UPDATE CUSTOMERS as c SET cust1= (select customer from test where custnum = c.custnum), cust2= NULL, street = (select address from test where custnum = c.custnum), town = (select town from tes

Re: [sqlite] SQLite Update With CTE

John McMahon wrote: > UPDATE CUSTOMERS as c > SET > cust1= (select customer from test where custnum = c.custnum), > cust2= NULL, > street = (select address from test where custnum = c.custnum), > town = (select town from test where custnum = c.custnum), > post

[sqlite] SQLite Update With CTE

Hi I am rewriting an old Perl script that selectively updates data from one table to another using this statement: UPDATE CUSTOMERS SET cust1= ?, cust2= NULL, street = ?, town = ?, postcode = ? WHERE custnum = ? I am intending to replace it with something li

Re: [sqlite] UPDATE database using parameters

On 7/22/17, 1:46 AM, "sqlite-users on behalf of Keith Medcalf" wrote: > Not very well. How do you think "drive by downloads" work? Javascript in > browsers is the most dangerous thing ever invented! I think the caps-lock key gives it a run for its money. __

Re: [sqlite] UPDATE database using parameters

On Friday, 21 July, 2017 20:05, Jens Alfke said: >> On Jul 21, 2017, at 1:01 PM, Keith Medcalf >> wrote: >> Just using a web browser has your machine executing god only knows >> what code generated by god only knows who doing god only knows what >> to your computer. Unless you have disabled th

Re: [sqlite] UPDATE database using parameters

> On Jul 21, 2017, at 1:01 PM, Keith Medcalf wrote: > > Just using a web browser has your machine executing god only knows what code > generated by god only knows who doing god only knows what to your computer. > Unless you have disabled that, of course. But that makes the web almost > comp

Re: [sqlite] UPDATE database using parameters

On Friday, 21 July, 2017 11:37, Jens Alfke wrote: > But anyone writing software that runs in a web server, > or that otherwise interacts with untrusted data, has to > pay attention to basic security practices. > And a fundamental one is that you don’t run code that > some untrusted person se

Re: [sqlite] UPDATE database using parameters

> On Jul 21, 2017, at 9:48 AM, John McKown wrote: > > ​And, just to interject a politically incorrect statement, any "programmer" > who does not use the safer interface is either __extremely__ ignorant, or > arrogantly stupid I wouldn’t put it that harshly, but I agree in spirit. If you’re jus

Re: [sqlite] UPDATE database using parameters

On Fri, Jul 21, 2017 at 11:07 AM, Jens Alfke wrote: > > > On Jul 21, 2017, at 6:45 AM, Peter Da Silva < > peter.dasi...@flightaware.com> wrote: > > > > Have a look at prepared statements and statement parameters. > > Agreed. > > PLEASE, PLEASE, do not try to splice parameters into SQL strings! An

Re: [sqlite] UPDATE database using parameters

> On Jul 21, 2017, at 6:45 AM, Peter Da Silva > wrote: > > Have a look at prepared statements and statement parameters. Agreed. PLEASE, PLEASE, do not try to splice parameters into SQL strings! Any mistakes in this code leave you vulnerable to SQL Injection Attacks, which are depressingly c

Re: [sqlite] UPDATE database using parameters

Using a straight PHP-level substitution like that performs the substitution before the SQL parser sees it. It’s also super dangerous if you’re not absolutely sure there’s no path for an untrusted agent to inject the name you’re selecting on. https://xkcd.com/327/ On 7/21/17, 3:42 AM, "sqlite-u

Re: [sqlite] UPDATE database using parameters

Have a look at prepared statements and statement parameters. Also, if you’re quoting strings you should use single quotes rather than double quotes. Double quotes just override the tokenizer, the result is still untyped and technically an identifier (the tokenizer actually marks it as TK_ID) unt

Re: [sqlite] UPDATE database using parameters

On 21 Jul 2017, at 11:14, Rowan Worth wrote: > On 21 July 2017 at 17:50, Tim Streater wrote: > >>$sql = "UPDATE Movies SET name = '$newname' where id=$newid"; >> >> Personally I don't like forcing PHP to scan strings so I tend to use >> concatentation, rewriting the last of these as: >> >>

Re: [sqlite] UPDATE database using parameters

On 21 July 2017 at 17:50, Tim Streater wrote: >$sql = "UPDATE Movies SET name = '$newname' where id=$newid"; > > Personally I don't like forcing PHP to scan strings so I tend to use > concatentation, rewriting the last of these as: > >$sql = 'UPDATE Movies SET name = \'' . $newname . '\'

Re: [sqlite] UPDATE database using parameters

On 21 Jul 2017 at 10:04, Edmondo Borasio wrote: > Hi Tim. > > It *almost* works.. > >$DbItemNameTest = "new name"; >$hIdTest = "1"; > >$db->exec ('UPDATE Anagrafica SET name = \'' . $DbItemNameTest . > '\' WHERE hospital_ID="1"'); //IT WORKS > $db->exec ('UPDATE Anagrafica

Re: [sqlite] UPDATE database using parameters

On 21 Jul 2017 at 09:58, Simon Slavin wrote: > On 21 Jul 2017, at 7:33am, Edmondo Borasio wrote: > >> $db->exec('UPDATE Movies SET name = "new movie" WHERE ID="4"'); > > Those are the wrong quotes. SQLite requires single quotes around literal > strings, and expects no quote around numbers. An

Re: [sqlite] UPDATE database using parameters

Hi Tim. It *almost* works.. $DbItemNameTest = "new name"; $hIdTest = "1"; $db->exec ('UPDATE Anagrafica SET name = \'' . $DbItemNameTest . '\' WHERE hospital_ID="1"'); //IT WORKS $db->exec ('UPDATE Anagrafica SET name = \'' . $DbItemNameTest . '\' WHERE hospital_ID=' . $hI

Re: [sqlite] UPDATE database using parameters

On 21 Jul 2017, at 7:33am, Edmondo Borasio wrote: > $db->exec('UPDATE Movies SET name = "new movie" WHERE ID="4"'); Those are the wrong quotes. SQLite requires single quotes around literal strings, and expects no quote around numbers. And PHP doesn’t care which quotes you use as long as they

Re: [sqlite] UPDATE database using parameters

Hi and thanks for your email. I am using PHP with SQLite on an Apache server. That statement was taken from some advice I got from a forum. I wasn't aware it was MySQL. I am new to SQLite and this is my first database. *"Table/column names cannot use parameters. You have to put it directly* *in

Re: [sqlite] UPDATE database using parameters

On 21 Jul 2017 at 07:33, Edmondo Borasio wrote: > I am updating a record of a SQLite database as follows: > > $db->exec('UPDATE Movies SET name = "new movie" WHERE ID="4"'); > > but instead of using name and ID I want to use some variables, $NewItemName > and $hId. > > Entering the variables as

Re: [sqlite] UPDATE database using parameters

Edmondo Borasio wrote: > $stmt->bind_param($p_name,$bind_value); This looks like PHP's MySQL driver. Which DB are you actually using? Anyway, I recommend you start with the examples from the manual, e.g., : $stmt = $db->prepare('SELECT b

Re: [sqlite] UPDATE database using parameters

Hi Thanks but it doesn't work. It's weird because the following works: $db->exec('UPDATE Anagrafica SET name = "new name" WHERE hID="1"'); //WORKS But this doesn't: $p_name ="new name"; $bind_value = "1"; $stmt = $conn->prepare('UPDATE Anagrafica SET name=? WHERE hID=?'); $s

Re: [sqlite] UPDATE database using parameters

On Fri Jul 21, 2017 at 06:33:55AM +, Edmondo Borasio wrote: > I am updating a record of a SQLite database as follows: > > $db->exec('UPDATE Movies SET name = "new movie" WHERE ID="4"'); > > but instead of using name and ID I want to use some variables, $NewItemName > and $hId. > > Entering t

[sqlite] UPDATE database using parameters

I am updating a record of a SQLite database as follows: $db->exec('UPDATE Movies SET name = "new movie" WHERE ID="4"'); but instead of using name and ID I want to use some variables, $NewItemName and $hId. Entering the variables as they are won't work. Neither using escape characters like \"$Db

Re: [sqlite] update or replace ...

On 2016/06/30 2:48 PM, Olivier Mascia wrote: Le 30 juin 2016 à 13:34, R Smith a écrit : MERGE dbo.xxx AS T USING dbo.yyy AS S ON T.SomeID = S.SomeID WHEN MATCHED AND T.ValueThatNeedsChanging = Oldvalue // or whatever checking is relevant THEN UPDATE SET T.ValueThatNeedsChanging =

Re: [sqlite] update or replace ...

On Thu, Jun 30, 2016 at 8:17 AM, Paul Egli wrote: > No chance for race conditions if data changes between the operations. > I should say "if data changes *or a read happens* between the operations. :-) ___ sqlite-users mailing list sqlite-users@mailingl

Re: [sqlite] update or replace ...

On Thu, Jun 30, 2016 at 7:48 AM, Olivier Mascia wrote: > > Le 30 juin 2016 à 13:34, R Smith a écrit : > > > > MERGE dbo.xxx AS T > > USING dbo.yyy AS S > > ON T.SomeID = S.SomeID > > WHEN MATCHED AND T.ValueThatNeedsChanging = Oldvalue // or whatever > checking is relevant > > THEN UPDATE SE

Re: [sqlite] update or replace ...

> Le 30 juin 2016 à 14:13, R Smith a écrit : > > There is no way to catch the outcome in the CLI that I know of, but that > doesn't matter. if you are writing scripts for the CLi and not programming > it, you can't possibly have SPEED as a paramount consideration, and if that's > the case, the

Re: [sqlite] update or replace ...

> Le 30 juin 2016 à 13:34, R Smith a écrit : > > MERGE dbo.xxx AS T > USING dbo.yyy AS S > ON T.SomeID = S.SomeID > WHEN MATCHED AND T.ValueThatNeedsChanging = Oldvalue // or whatever checking > is relevant > THEN UPDATE SET T.ValueThatNeedsChanging = NewValue > WHEN NOT MATCHED >TH

Re: [sqlite] update or replace ...

On 2016/06/30 12:00 PM, Olivier Mascia wrote: Besides, if you ever have to write a scripts for the command line tool, you're stuck trying to emulate that semantic. Or can we check by script the outcome of the UPDATE? There is no way to catch the outcome in the CLI that I know of, but that

Re: [sqlite] update or replace ...

On 2016/06/30 10:54 AM, Olivier Mascia wrote: Does the "INSERT OR REPLACE" syntax not provide what you are looking for? Absolutely not. "OR REPLACE", as Ryan and Simon pointed out clearly, is a conflict resolution mechanism. 1) The insert would have to trigger a constraint violation to execu

Re: [sqlite] update or replace ...

On 30 Jun 2016, at 10:51am, Olivier Mascia wrote: > INSERT OR IGNORE ... > followed by > UPDATE ... Of course. That's what I actually did when I did it, but I'd forgotten the best way. Your way means you don't have to check any error codes. Thanks for reminding me. Simon.

Re: [sqlite] update or replace ...

Whatever the case, it will be, on average, 1.5x time less efficient than a 'I-Hope-Would-Be' UPSERT. It all depends on the probability of INSERT to fail.  In the scenario, where updates are frequent and inserts are rare, INSERT + UPDATE approach takes ~2x performance overhead. In most cases we i

Re: [sqlite] update or replace ...

> Le 30 juin 2016 à 11:17, Clemens Ladisch a écrit : > > A better way is to try the UPDATE first, and if the data was not found > (number of affected rows is zero), do the INSERT. Doesn't even need > a comment. Indeed. That is precisely what we do. And what is not so easy in complex programmin

Re: [sqlite] update or replace ...

> Le 30 juin 2016 à 11:01, Simon Slavin a écrit : > >> Of course writing straight code in C/C++ it's rather simple to emulate >> situations where you want to update some values in an existing row, creating >> the row if needed. > > The standard way of doing this is to do two commands: > > 1)

Re: [sqlite] update or replace ...

Simon Slavin wrote: > On 30 Jun 2016, at 8:24am, Olivier Mascia wrote: >> Of course writing straight code in C/C++ it's rather simple to emulate >> situations where you want to update some values in an existing row, >> creating the row if needed. > > The standard way of doing this is to do two com

Re: [sqlite] update or replace ...

On 30 Jun 2016, at 8:24am, Olivier Mascia wrote: > Of course writing straight code in C/C++ it's rather simple to emulate > situations where you want to update some values in an existing row, creating > the row if needed. The standard way of doing this is to do two commands: 1) INSERT withou

Re: [sqlite] update or replace ...

> Le 30 juin 2016 à 10:06, no...@null.net a écrit : > >> I'd love to have some equivalent to the UPDATE OR INSERT statement >> (or variation on it) that some other engines expose. But clearly > > Does the "INSERT OR REPLACE" syntax not provide what you are looking > for? Absolutely not. "OR RE

Re: [sqlite] update or replace ...

On Thu Jun 30, 2016 at 09:24:36AM +0200, Olivier Mascia wrote: > I'd love to have some equivalent to the UPDATE OR INSERT statement > (or variation on it) that some other engines expose. But clearly Does the "INSERT OR REPLACE" syntax not provide what you are looking for? -- Mark Lawrence

Re: [sqlite] update or replace ...

> Le 29 juin 2016 à 18:56, Olivier Mascia a écrit : > > Clearly it does nothing. > Does this fit the intended behavior? > Would that mean 'or replace' in the context of an update statement is a void > operation? Thanks Ryan and Simon for your answers. Indeed I forgot the OR REPLACE|FAIL|IGNOR

Re: [sqlite] update or replace ...

On 29 Jun 2016, at 5:56pm, Olivier Mascia wrote: > What's the expected behavior of statement "update or replace ..."? > (http://sqlite.org/lang_update.html) > > create table T(K integer primary key, V text); > update or replace T set V='data' where K=1; > > Clearly it does nothing. > Does thi

Re: [sqlite] update or replace ...

On 2016/06/29 6:56 PM, Olivier Mascia wrote: Dear all, What's the expected behavior of statement "update or replace ..."? (http://sqlite.org/lang_update.html) create table T(K integer primary key, V text); update or replace T set V='data' where K=1; Clearly it does nothing. Does this fit th

[sqlite] update or replace ...

Dear all, What's the expected behavior of statement "update or replace ..."? (http://sqlite.org/lang_update.html) create table T(K integer primary key, V text); update or replace T set V='data' where K=1; Clearly it does nothing. Does this fit the intended behavior? Would that mean 'or replace'

Re: [sqlite] Update DataGrid and Save to database

Most people seem to prefer black these days, but white is cooler. Reflects, rather than absorbs, heat. Of course, if you live up north that might be a bad thing. Here in the south, all is well... On Mon, Jun 13, 2016 at 9:31 AM, Chris Locke wrote: > Great analogy. PS: What colour seat covers

Re: [sqlite] Update DataGrid and Save to database

Great analogy. PS: What colour seat covers should I be using if I have a Ford? On Mon, Jun 13, 2016 at 2:11 PM, jumper wrote: > Thank you for the advice/information. I just solved the issue about a > minute ago. How can I stop getting new replies? > > > On 6/13/2016 8:08 AM, R Smith wrote: > >>

Re: [sqlite] Update DataGrid and Save to database

On 2016/06/13 3:09 PM, jumper wrote: I just solved it only seconds ago. Can't believe it after all this time. How do I stop getting messages now that I have it working? Have you read this e-mail all the way to the bottom? :) ___ sqlite-users mailing

Re: [sqlite] Update DataGrid and Save to database

Thank you for the advice/information. I just solved the issue about a minute ago. How can I stop getting new replies? On 6/13/2016 8:08 AM, R Smith wrote: On 2016/06/13 2:48 PM, jumper wrote: New to SQLite and DataGrids. I need to know how to get an adapter, table and dataset when app first

Re: [sqlite] Update DataGrid and Save to database

I just solved it only seconds ago. Can't believe it after all this time. How do I stop getting messages now that I have it working? thank you so much. On 6/13/2016 8:04 AM, Chris Brody wrote: I wonder if the answers following link could help you: http://stackoverflow.com/questions/19617368/sqli

Re: [sqlite] Update DataGrid and Save to database

On 2016/06/13 2:48 PM, jumper wrote: New to SQLite and DataGrids. I need to know how to get an adapter, table and dataset when app first comes up. Then when someone updates the DataGrid I need to save the changes to the database. I've been working on this for days and can't do it. Could some

Re: [sqlite] Update DataGrid and Save to database

I wonder if the answers following link could help you: http://stackoverflow.com/questions/19617368/sqlite-database-and-datagrid I found this by a quick Google search. For the future please explain the context, show that you have done some basic research, show what you have found, and show specific

[sqlite] Update DataGrid and Save to database

New to SQLite and DataGrids. I need to know how to get an adapter, table and dataset when app first comes up. Then when someone updates the DataGrid I need to save the changes to the database. I've been working on this for days and can't do it. Could someone please post the code for how to do

Re: [sqlite] UPDATE statement without FROM clause

Hi, of course there is in general a difference between syntax complexity and performance but unfortunately not in this case. And the „just“ is very often the most difficult part. Regards, Hartwig > Am 2016-06-07 um 07:39 schrieb James K. Lowden : > > On Sat, 4 Jun 2016 18:18:36 +0200 > skywin

Re: [sqlite] UPDATE statement without FROM clause

On Sat, 4 Jun 2016 18:18:36 +0200 skywind mailing lists wrote: > At the moment I have to run something like: > > UPDATE A SET item1=(SELECT B.item FROM B WHERE B.ID=A.ID),... > itemN=... WHERE EXISTS (SELECT 1 FROM B WHERE B.ID=A.ID); > > Using a FROM clause I just need one scan through B (at l

Re: [sqlite] UPDATE statement without FROM clause

ts.sqlite.org > [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von skywind > mailing lists > Gesendet: Samstag, 04. Juni 2016 18:19 > An: SQLite mailing list > Betreff: Re: [sqlite] UPDATE statement without FROM clause > Hi, > why? At the moment I have to

Re: [sqlite] UPDATE statement without FROM clause

the update. -Ursprüngliche Nachricht- Von: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von skywind mailing lists Gesendet: Samstag, 04. Juni 2016 18:19 An: SQLite mailing list Betreff: Re: [sqlite] UPDATE statement

Re: [sqlite] UPDATE statement without FROM clause

You can simulate either a two-pass or one-pass UPDATE SET ... FROM , WHERE By doing one or the other of the following (depending on whether you want one-pass or two-pass). for a one-pass update: BEGIN IMMEDIATE; SELECT .rowid, FROM WHERE fetch a row UPDATE SET x=?, ... WHERE rowid=?

Re: [sqlite] UPDATE statement without FROM clause

At 23:34 04/06/2016, you wrote: On 4 Jun 2016, at 10:15pm, Jean-Christophe Deschamps wrote: > Can't the same update be done more efficiently with a CTE? The command inside the WITH has to be a SELECT command. Definitely not as Ryan pointed out, and as the help file clearly states otherwis

Re: [sqlite] UPDATE statement without FROM clause

On 2016/06/04 11:34 PM, Simon Slavin wrote: On 4 Jun 2016, at 10:15pm, Jean-Christophe Deschamps wrote: Can't the same update be done more efficiently with a CTE? The command inside the WITH has to be a SELECT command. I wonder if there's a good reason for that. If the command inside WITH

Re: [sqlite] UPDATE statement without FROM clause

On 4 Jun 2016, at 10:15pm, Jean-Christophe Deschamps wrote: > Can't the same update be done more efficiently with a CTE? The command inside the WITH has to be a SELECT command. I wonder if there's a good reason for that. If the command inside WITH could make changes to the database the resul

Re: [sqlite] UPDATE statement without FROM clause

At 18:18 04/06/2016, you wrote: Hi, why? At the moment I have to run something like: UPDATE A SET item1=(SELECT B.item FROM B WHERE B.ID=A.ID),... itemN=... WHERE EXISTS (SELECT 1 FROM B WHERE B.ID=A.ID); Using a FROM clause I just need one scan through B (at least in principle). Now, I nee

Re: [sqlite] UPDATE statement without FROM clause

Is there some absolute requirement that it all be done in SQL? Depending on the number of "items", it'd probably be faster in a loop in code. Even in MSSQL Server using TSQL, you're better off using a cursor for that sort of thing. I only use UPDATE FROM when I need a join to formulate the WHE

Re: [sqlite] UPDATE statement without FROM clause

Hi, why? At the moment I have to run something like: UPDATE A SET item1=(SELECT B.item FROM B WHERE B.ID=A.ID),... itemN=... WHERE EXISTS (SELECT 1 FROM B WHERE B.ID=A.ID); Using a FROM clause I just need one scan through B (at least in principle). Now, I need N+1 scans. Regards, Hartwig > A

Re: [sqlite] UPDATE statement without FROM clause

If SQLite implemented the FROM it would just be a translation into the complex and slow statements you want to avoid. Gerry Snyder On Jun 4, 2016 9:19 AM, "skywind mailing lists" wrote: > Hi, > > I am using quite often SQL statements that update the data of one table > with data from another tab

[sqlite] UPDATE statement without FROM clause

Hi, I am using quite often SQL statements that update the data of one table with data from another table. This leads to some quite complex (and slow) statements because SQLite3 is not supporting a FROM clause in update statements. I am just wondering why the FROM clause is not supported by SQLi

[sqlite] UPDATE/open/close blocked while executing SELECT

On Monday, 18 April, 2016 08:47 Olivier Mascia wrote: > Just to clarify: you mean compiled using SQLITE_CONFIG_MULTITHREAD (and > not SQLITE_CONFIG_SERIALIZED), is that it? > Then, if using threads in the application, each thread has its own > connection (or multiple connections) but no thread

[sqlite] UPDATE/open/close blocked while executing SELECT

> Le 18 avr. 2016 ? 15:10, Detlef Golze a ?crit : > > thank you for the hints. I think I do it correctly, I compiled with > SQLITE_CONFIG_MULTITHREAD and attach the database with the URI option > cache=shared. Do you really *need* to use shared cache in favor of private cache? There are more

[sqlite] UPDATE/open/close blocked while executing SELECT

Detlef Golze wrote: > I attach the database with the URI option cache=shared. This makes all threads share a single connection. Don't do it. Regards, Clemens

[sqlite] UPDATE/open/close blocked while executing SELECT

On 18 Apr 2016, at 2:10pm, Detlef Golze wrote: > I compiled with SQLITE_CONFIG_MULTITHREAD That bit's okay. > and attach the database with the URI option cache=shared Try turning that bit off and see if it results in an overall speed-up. Simon.

[sqlite] UPDATE/open/close blocked while executing SELECT

> Le 18 avr. 2016 ? 14:17, Detlef Golze a ?crit : > > Hi, > > I am using SQLite C amalgamation version 3.08.11.01 compiled for Multi > Threading Mode and using WAL Journal Mode. > > Sometimes I need to execute a SELECT statement which takes a very long time > to complete. I realized that dur

[sqlite] UPDATE/open/close blocked while executing SELECT

> Detlef Golze wrote: > > I attach the database with the URI option cache=shared. > > This makes all threads share a single connection. Don't do it. > > > Regards, > Clemens That was indeed the problem. During my initial tests this option provided the best performance. I probably need to re-t

[sqlite] UPDATE/open/close blocked while executing SELECT

, April 18, 2016 2:47 PM To: SQLite mailing list Subject: Re: [sqlite] UPDATE/open/close blocked while executing SELECT > Le 18 avr. 2016 ? 14:17, Detlef Golze a ?crit : > > Hi, > > I am using SQLite C amalgamation version 3.08.11.01 compiled for Multi > Threading Mode and

[sqlite] UPDATE/open/close blocked while executing SELECT

Hi, I am using SQLite C amalgamation version 3.08.11.01 compiled for Multi Threading Mode and using WAL Journal Mode. Sometimes I need to execute a SELECT statement which takes a very long time to complete. I realized that during that time other functions are blocked for a significant amount o

[sqlite] Awesome SQLite Update - New Book - Getting Started with SQL(lite and SQLiteStudio)

Hello, For you enjoyment I have added three more entries to the Awesome SQLite [1] collection: - sqlite-web (github: coleifer/sqlite-web) by Charles Leifer -- a web-based SQLite database browser written in Python - sqliteweb (github: hypebeast/sqliteweb) by Sebastian Ruml -- a web-based SQLi

[sqlite] Awesome SQLite Update - New Book - Getting Started with SQL(lite and SQLiteStudio)

Hello, I've updated the Awesome SQLite [1] collection over at Planet Open Data. What's news? Added a new beginner's book: Getting Started with SQL - A Hands-On Approach for Beginners - by Thomas Nield; 2016; O'Reilly; 134 pages -- learn SQL with SQLite and SQLiteStudio Anything missing?

[sqlite] C# + SQLite - Update/Insert using Transaction is slower than without.

-Original Message- > From: sqlite-users-bounces at mailinglists.sqlite.org > [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Chris > Prakoso > Sent: Tuesday, February 9, 2016 7:56 AM > To: SQLite mailing list > Subject: Re: [sqlite] C# + SQLite - Update/In

[sqlite] C# + SQLite - Update/Insert using Transaction is slower than without.

Steven, I don't use any back-end, the code I pasted here IS my back-end. I opted for direct SQLite connection. So I don't use EF6 nor Linq. Chris On Tue, Feb 9, 2016 at 1:47 PM, Steven M. McNeese < steven.mcneese at freedomparkdfw.com> wrote: > Chris, > > What are you using in c# for SQLite b

[sqlite] C# + SQLite - Update/Insert using Transaction is slower than without.

Chris Prakoso wrote: > public bool UpdateData(string tableName, Dictionary > fields, List whereKeys) > { > ... > using (SQLiteTransaction transaction = > conn.BeginTransaction()) > { > ... >

[sqlite] C# + SQLite - Update/Insert using Transaction is slower than without.

Ok. Got it. Now, if only I can get that multiple rows update working on my code, it would be perfect. Thanks a lot, Chris On Tue, Feb 9, 2016 at 12:07 PM, Clemens Ladisch wrote: > Chris Prakoso wrote: > > public bool UpdateData(string tableName, > Dictionary fields, List whereKeys) > >

[sqlite] C# + SQLite - Update/Insert using Transaction is slower than without.

Chris Prakoso wrote: > I've been testing the performance of my Insert/Update using > Transaction and without, and I found that it is quicker when I don't > use it. Show the code. Regards, Clemens

[sqlite] C# + SQLite - Update/Insert using Transaction is slower than without.

Here they are: public bool UpdateData(string tableName, Dictionary fields, List whereKeys) { bool result = false; string sql = ""; List fieldList = new List(); List whereKeyList = new List(); int rowsUpdated = 0;

[sqlite] C# + SQLite - Update/Insert using Transaction is slower than without.

Hi all, I've been testing the performance of my Insert/Update using Transaction and without, and I found that it is quicker when I don't use it. Anybody has an insight on this? Thanks a lot, Chris

[sqlite] C# + SQLite - Update/Insert using Transaction is slower than without.

: Tuesday, February 9, 2016 7:56 AM To: SQLite mailing list Subject: Re: [sqlite] C# + SQLite - Update/Insert using Transaction is slower than without. Steven, I don't use any back-end, the code I pasted here IS my back-end. I opted for direct SQLite connection. So I don't use EF6 nor Linq.

[sqlite] C# + SQLite - Update/Insert using Transaction is slower than without.

Chris, What are you using in c# for SQLite back end? Ado.net? Linq. Let me know and I can help you with bulk inserts. Sent from my iPhone > On Feb 9, 2016, at 6:13 AM, Chris Prakoso wrote: > > Ok. Got it. > Now, if only I can get that multiple rows update working on my code, it > would be

  1   2   3   4   5   6   7   8   >