CTE's Can Be Useful in Update Queries
An update script often looks something like the following.
UPDATE tablename
SET some_column = (subquery1)
WHERE some_(possibly other column) IN (subquery2)
The two subqueries often contain a great deal of code in common. It had never occurred to me until couple of days ago, but the common code can probably be implemented by a CTE. Most of my use of CTE's has involved recursive CTE's, but it's pretty obvious that there are good use cases for CTE's that are not recursive.
In this case, I was having a little trouble with query where I was helping another user to add information from Web tags to citations. So I asked ChatGPT for help. It suggested using a CTE. I wrote the actual CTE that is in my script, but I copied the idea from ChatGPT. The script in question is attached. It is very specific to this particular use case and as such is probably not of much general interest, other than the use of a CTE to make an UPDATE script read a little more cleanly.
Uploaded files:That's a very short and neat solution to what I presume was the request from @jlodge .
In part, this reply was to test how long a reply takes to being published after having done many things on the WordPress side of this website. But I realised that the first time I read your post, it was on my phone and I didn't look at the query itself because I concurred with what you said and it's hard to read a query on a small screen.
Yes, @thejerrybryan came to my rescue once again!
Tom's question has already been answered correctly in the affirmative. But I did want to make one additional comment about the SQLite stylistic underpinnings of this query. Namely, it has to update CitationTable.Fields.
I have done a lot of work through the years on scripts which update SourceTable.Fields and CitationTable.Fields. They are both a mess to deal with because they are XML strings stored as text within a BLOB field. The BLOB field is not as big an issue as it might sound like in this case because SQLite is so loosy-goosy about its enforcement of data typing rules. Namely, it doesn't enforce data typing rules at all, as do nearly all other relational databases. So you can use any of the string functions on BLOB fields without difficulty, and you can leave the results as TEXT instead of BLOB and RM will use the resulting data just fine.
But I'm a C++ person who believes in strict data typing. So my SQLite scripts have usually tried to be careful to cast the BLOB to TEXT before applying the string functions, and equally careful to cast the final results back to BLOB. But this time, I decided to accept the fact that a string function applied to a BLOB works just fine and results in TEXT data. It's sort of like an implicit CAST. So the only explicit CAST I chose to include in the script was the one to cast the final result back into BLOB for storage in the database. Doing it that way made the script a lot less messy to write and a lot less messy to look at and understand.
I had to grit my teeth a little bit to do it that way. But even I have to admit that C++ (and even C, for that matter) actually have a lot of situations where data type conversions can be implicit without a CAST instead of explicit with a CAST.
AI (Gemini) said the following :
Which one to use?
While implicit casting can be convenient, it's generally recommended to use explicit casting when the data types are ambiguous. This improves code clarity and reduces the risk of unexpected behavior. Explicit casting also helps enforce data integrity and makes the code more portable and easier to debug. For instance, if you have a column with different data types, explicitly casting them to a single type before performing an operation can prevent errors.