如何在SQLite的更新/删除中使用CTE？

本文介绍了如何在SQLite的更新/删除中使用CTE？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

SQLite现在具有CTE，并且文档表示您可以将其与插入，更新和删除查询一起使用-但

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.

我可以通过insert-select找出CTE如何应用于插入。但是如何在没有从句的情况下如何在更新或删除中使用它们？

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?

推荐答案

CTE可以在子查询中使用：

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;