在标准SQL中如何通过CTE删除关联表中的冗余数据？-SQL-PHP中文网

cte本身不能直接用于delete，标准sql禁止delete from cte；安全通用做法是用cte输出待删主键，再通过in或exists在delete中引用，确保逻辑清晰、跨库兼容、可预览可审计。

在标准sql中如何通过cte删除关联表中的冗余数据？

CTE 本身不能直接用于 DELETE（标准 SQL 的限制）

标准 SQL（如 SQL:2016）中，WITH 子句定义的 CTE 只能被 SELECT、INSERT、UPDATE、DELETE 引用——但**仅当该语句本身支持 CTE 作为目标来源时才成立**。关键点在于：绝大多数数据库不支持 DELETE ... FROM cte_name 这种写法（它不是标准 SQL，而是某些方言的扩展）。比如 PostgreSQL 允许 DELETE USING 配合 CTE，SQL Server 支持 DELETE FROM cte（但有严格限制），而 MySQL 8.0+ 要求 CTE 必须是可更新的（且不能含聚合、去重、连接等），实际几乎不可用于删除关联数据。

安全删除关联冗余数据的通用做法：用 CTE 定义“要删的主键”，再 JOIN 到原表

真正跨数据库兼容、语义清晰、不易误删的方式，是把 CTE 当作“待删 ID 集合”，然后在 DELETE 中通过子查询或 IN/EXISTS 关联。例如，删除 orders 表中无对应 customer 的冗余记录：

WITH orphaned_orders AS (
  SELECT o.order_id
  FROM orders o
  LEFT JOIN customers c ON o.customer_id = c.customer_id
  WHERE c.customer_id IS NULL
)
DELETE FROM orders
WHERE order_id IN (SELECT order_id FROM orphaned_orders);

orphaned_orders 只负责明确、可验证地找出冗余行的主键，逻辑隔离，便于调试
必须用 IN 或 EXISTS，不能写成 DELETE FROM orders JOIN orphaned_orders ...（语法错误）
若 order_id 可能为 NULL，IN 会失效，此时改用 EXISTS 更稳妥
大表务必在 order_id 和 customer_id 上建索引，否则子查询可能全表扫描

MySQL 8.0+ 的特殊陷阱：不要误信“CTE 可更新”

MySQL 文档提到“可更新 CTE”，但实际限制极严：CTE 不能含 DISTINCT、GROUP BY、窗口函数、集合操作，且必须单表、无派生列。一旦涉及关联判断冗余（比如找未被引用的父记录），CTE 几乎必然不可更新。试图这样写会报错：

WITH unused_cats AS (
  SELECT id FROM categories c
  WHERE NOT EXISTS (SELECT 1 FROM products p WHERE p.category_id = c.id)
)
DELETE FROM categories WHERE id IN (SELECT id FROM unused_cats); -- ✅ 安全可行
-- DELETE FROM unused_cats; -- ❌ 报错：Unknown table 'unused_cats' in MULTI DELETE

MySQL 不允许对非单表、含子查询的 CTE 执行 DELETE
即使 CTE 看似简单，只要底层涉及外键检查或触发器，也可能隐式不可更新
永远优先用 WHERE ... IN (CTE) 模式，而不是依赖方言特性的“直接删 CTE”

PostgreSQL 和 SQL Server 的“捷径”有代价

PostgreSQL 支持 DELETE ... USING，SQL Server 允许 DELETE FROM cte，但它们都要求 CTE 是“可寻址的”——即最终映射到基表的某一行。例如在 PostgreSQL 中：

WITH dupes AS (
  SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) AS rn
  FROM users
)
DELETE FROM users
USING dupes
WHERE users.id = dupes.id AND dupes.rn > 1;