I've been able to get my code runing by changing it to the following:
update relation
set wamapname = (select wrk.mapname
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
where relation.udb_key = udb.udb_key
and relation.tablename = rel.tablename)
where queryid = 1;
This is using a correlated sub-query to ensure that I update the correct rows
in the target table (the 'relation' table). I have to do some more testing but
so far it looks to be working correctly.
So I guess now my question is when/how would I use a CTE in an UPDATE statement?
My reading of the syntax diagram (http://www.sqlite.org/lang_update.html )
shows that the CTE is valid syntax.
Can anyone please give me an example (doesn’t need to use my tables) where a
CTE can be used with the UPDATE?
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 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 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
