How to use CTE's with update/delete on SQLite?
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;
Another slightly more concise way of using CTE in SQlite 3.15.0 and up.
WITH t (id, name, nickname) AS (VALUES (1, "bob", "bobby"), (2, "john", "johnny"))
UPDATE user
SET (name, nickname) = (
SELECT name, nickname
FROM t WHERE user.id = t.id
)
WHERE id IN (SELECT id FROM t)
Works great as a bulk update!