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

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

Re: [sqlite] UPDATE statement without FROM clause

qlite-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 <sqlite-users@mailinglists.sqlite.org> > Betreff: Re: [sqlite] UPDATE statemen

Re: [sqlite] UPDATE statement without FROM clause

etreff: 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

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

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

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

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

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

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

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 >

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 >

[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