Syntax error using CTE and UPDATE

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Syntax error using CTE and UPDATE

Dave 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

                    ,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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Syntax error using CTE and UPDATE

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 (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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Syntax error using CTE and UPDATE

Dave Wellman
In reply to this post by Dave Wellman
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:[hidden email]] 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Syntax error using CTE and UPDATE

Dave Wellman
In reply to this post by R Smith
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:[hidden email]] On Behalf Of R Smith
Sent: 04 September 2017 11:37
To: [hidden email]
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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Syntax error using CTE and UPDATE

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 asked in that (I'm impressed, how did you do that ?)

No special powers I'm afraid, the question pops up from time to time, so
sometimes we answer preemptively the typical question(s) in stead of
just responding to the actual question.

> 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.

Yes, some DBs do allow a JOIN for INSERTs and some even for UPDATEs
though I think that is provided as a matter of convenience rather than
DB SQL Standard, and when you have the luxury of very large code size
for the DB Engine, a very nice-to-have; but the -Lite part of SQLite
means it has to watch its weight, so correlated sub-queries it is.

That said, a CTE is essentially just a sub-query that, in stead of being
correlated, pretends to be a table so you are free to reference it in a
correlated manner (or any other manner) in the subsequent SQL statement
parts. Which is what my changes to your code basically did.

There is one very important difference between a CTE and a Sub-Query - A
CTE can create data out of thin air by virtue of the "Recursive"
functionality. Something that no other query or table or view can do.

Let's say you have a table with calendar events and dates in it and you
would like to list all the days in this month that ISN'T in the
calendar, how would you do that with a normal query?

With a CTE it's easy:

WITH AllDays(DayIdx, DayDate) AS (
     SELECT 1, date('now','start_of_month')
     UNION ALL
     SELECT DayIdx+1, date(DayDate,'+1 days') FROM AllDays WHERE DayIdx < 31
)
SELECT DayDate
   FROM AllDays
   LEFT JOIN MyCalendar AS C ON C.DayDate = AllDays.DayDate
  WHERE C.DayDate IS NULL
;

This will list all the days of the current month that doesn't have a
corresponding entry in the MyCalendar table yet. You can use it to
INSERT or UPDATE any other table like a Boss without resorting to
programming to find missing dates.

Just an example, and I haven't tested it since this is simply a
theoretical discussion so the syntax might be off, but the principle stands.

The "Recursive" engine kicks into gear after the UNION [ALL] statement
when you reference the table to itself, thereby extrapolating itself
until stopped by virtue of the WHERE filter. Note that you can get stuff
to recurse infinitely (needing a manual kill process) if the WHERE
restriction is missing, so be careful. (There's obviously a lot more to
be said about CTEs outside the scope of this post, but I encourage you
to dig into it, it's quite fun. See: http://sqlite.org/lang_with.html )


Cheers!
Ryan



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Syntax error using CTE and UPDATE

R Smith
In reply to this post by Dave Wellman
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 (
     SELECT stuff...
)
UPDATE t SET (a, b, c) = (SELECT ta, tb, tc FROM CTE WHERE CTE.ID = t.ID)
;

INSERT of course can utilize a sub query or VALUES clause directly,
which provide much the same functionality.

Read more here: http://sqlite.org/rowvalue.html

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Syntax error using CTE and UPDATE

Dave Wellman
Now that could be really useful for some things that I'm doing.

Many thanks.

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:[hidden email]] On Behalf Of R Smith
Sent: 04 September 2017 12:49
To: [hidden email]
Subject: Re: [sqlite] 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:

WITH CTE(ID, ta, tb, tc) AS (
     SELECT stuff...
)
UPDATE t SET (a, b, c) = (SELECT ta, tb, tc FROM CTE WHERE CTE.ID = t.ID)
;

INSERT of course can utilize a sub query or VALUES clause directly,
which provide much the same functionality.

Read more here: http://sqlite.org/rowvalue.html

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users