How to use CTE's with update/delete on SQLite?

Question

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?

Answer 1

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!