Re: [sqlite] UPDATE statement without FROM clause

2016-06-04 Thread R Smith
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

2016-06-04 Thread Simon Slavin
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

2016-06-04 Thread Jean-Christophe Deschamps
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

2016-06-04 Thread Brad Stiles
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

2016-06-04 Thread skywind mailing lists
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

2016-06-04 Thread Gerry Snyder
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

2016-06-04 Thread skywind mailing lists
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