How to use CTE's with update/delete on SQLite?
SQLite now has CTEs, and the documentation says you can use it with insert, update and delete queries -- but only gives examples of select statements.
I can figure out how CTEs apply to inserts, via insert-select; but how can we use them in update or delete, where there is no from-clause?
CTEs can be used in subqueries:
WITH NewNames(ID, Name) AS (...) UPDATE MyTable SET Name = (SELECT Name FROM NewNames WHERE ID = MyTable.ID); WITH IDsToDelete AS (...) DELETE FROM MyTable WHERE ID IN IDsToDelete;