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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
