Re: [sqlite] Syntax error using CTE and UPDATE

2017-09-04 Thread David Wellman
] Syntax error using CTE and UPDATE One last thing I forgot to mention, on the topic of making INSERT and UPDATE easy - If you are using SQLite 3.15 or later, you can use Row-value functionality to UPDATE several fields in one go from a sub query. An example of how such an update query might look

Re: [sqlite] Syntax error using CTE and UPDATE

2017-09-04 Thread R Smith
One last thing I forgot to mention, on the topic of making INSERT and UPDATE easy - If you are using SQLite 3.15 or later, you can use Row-value functionality to UPDATE several fields in one go from a sub query. An example of how such an update query might look: WITH CTE(ID, ta, tb, tc) AS

Re: [sqlite] Syntax error using CTE and UPDATE

2017-09-04 Thread R Smith
On 2017/09/04 12:46 PM, David Wellman wrote: Hi Ryan, Thanks for that. It is certainly valid syntax and I'll do some testing to check that it gives me the correct answer. It's always a pleasure. Your email has 'crossed in the post' with my second one and you've answered something that I

Re: [sqlite] Syntax error using CTE and UPDATE

2017-09-04 Thread David Wellman
: Re: [sqlite] Syntax error using CTE and UPDATE You are essentially trying to use a CTE (which for the intents and purposes of the UPDATE SQL is just the same as using any other table) inside an UPDATE statement as if it is the main referenced table. In an UPDATE or INSERT however, there can

Re: [sqlite] Syntax error using CTE and UPDATE

2017-09-04 Thread David Wellman
- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of David Wellman Sent: 04 September 2017 11:23 To: 'SQLite mailing list' Subject: [sqlite] Syntax error using CTE and UPDATE Hi, (I have a feeling that this will be depressingly simple - but I just can't see

Re: [sqlite] Syntax error using CTE and UPDATE

2017-09-04 Thread R Smith
You are essentially trying to use a CTE (which for the intents and purposes of the UPDATE SQL is just the same as using any other table) inside an UPDATE statement as if it is the main referenced table. In an UPDATE or INSERT however, there can only ever be 1 single main referenced table

[sqlite] Syntax error using CTE and UPDATE

2017-09-04 Thread David Wellman
Hi, (I have a feeling that this will be depressingly simple - but I just can't see it right now.) The following code is failing with: Error: near line 3: no such column: dtls.mapname2 explain with dtls as (select distinct wrk.mapname as mapname2 ,udb.udb_key