How to use CTE with update / delete on SQLite?

Question

SQLite now has a CTE, and the documentation says that you can use it with inserts, update and delete queries, but only gives examples of select statements.

I can understand how CTEs are applied to inserts via insert-select; but how can we use them when updating or deleting if there is no offer from an offer?

Answer 1

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;