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
