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.
Your email has 'crossed in the post' with my second one and you've answered something that I asked in that (I'm impressed, how did you do that ?) I understand that the UPDATE and INSERT can only have one main (or target) table, I completely agree with that. I didn't think of the CTE as being 'the main referenced' (i.e. target) table. I have used a different dbms for 20+ years (Teradata) and that allows joins in UPDATEs (coded in a WHERE clause). It also allows use of a CTE albeit a slightly different syntax. Once again, thanks for your help. Cheers, Dave Ward Analytics Ltd - information in motion Tel: +44 (0) 118 9740191 Fax: +44 (0) 118 9740192 www: http://www.ward-analytics.com Registered office address: The Oriel, Sydenham Road, Guildford, Surrey, United Kingdom, GU1 3SR Registered company number: 3917021 Registered in England and Wales. -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of R Smith Sent: 04 September 2017 11:37 To: sqlite-users@mailinglists.sqlite.org Subject: 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 only ever be 1 single main referenced table (i.e. no joins allowed) and any references to values in another table has to be by virtue of a sub-query (typically a correlated one). I don't have your DB so I cannot test the answer, but it should work when changed to: with dtls as (select distinct wrk.mapname as mapname2 ,udb.udb_key ,rel.tablename ,rel.queryid from waPSM_TABLE_MAPNAME_WRK as wrk inner join relation as rel on wrk.queryid = 1 and wrk.queryid = rel.queryid and wrk.tablename = rel.name inner join User_Database as udb on wrk.databasename = udb.udb_name and rel.udb_key = udb.udb_key) update relation set wamapname = ( SELECT dtls.mapname2 FROM dtls WHERE dtls.queryid = relation.queryid AND dtls.udb_key = relation.udb_key AND dtls.tablename = relation.tablename LIMIT 1 ) ; Hope that works, Cheers! Ryan On 2017/09/04 12:23 PM, David Wellman wrote: > 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 > > ,rel.tablename > > ,rel.queryid > > from waPSM_TABLE_MAPNAME_WRK as wrk > > inner join relation as rel > > on wrk.queryid = 1 > > and wrk.queryid = rel.queryid > > and wrk.tablename = rel.name > > inner join User_Database as udb > > on wrk.databasename = udb.udb_name > > and rel.udb_key = udb.udb_key) > > update relation > > set wamapname = dtls.mapname2 > > where queryid = dtls.queryid > > and udb_key = dtls.udb_key > > and tablename = dtls.tablename; > > > > I'm running it using the windows 32-bit shell program v3.20.1 > > > > If I just run the CTE component by itself it runs fine. > > > > What am I missing? > > > > All help greatly appreciated, > > Dave > > > > > > > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
