Teradata中使用row_number()去重时出现语法错误求助

错误根源 #

Teradata不支持直接通过DELETE FROM CTE的语法删除数据，你当前的写法是SQL Server等数据库的兼容语法，Teradata的WITH子句后无法直接跟DELETE操作，这是导致[3707]语法错误的核心原因。

排查与解决步骤 #

• 修正DML语法：Teradata中需要将CTE作为数据源，关联原表执行删除，推荐两种写法：

  1. IN子句关联删除：

  WITH cte AS (
      SELECT 
          Id, 
          Proname, 
          Cityname, 
          Companyname,
          ItemsNo,
          ROW_NUMBER() OVER (
              PARTITION BY Id, Proname, Cityname, Companyname
              ORDER BY Id, Proname, Cityname, Companyname
          ) AS row_num
      FROM dba.tabdupes
  )
  DELETE FROM dba.tabdupes
  WHERE (Id, Proname, Cityname, Companyname, ItemsNo) IN (
      SELECT Id, Proname, Cityname, Companyname, ItemsNo
      FROM cte
      WHERE row_num > 1
  );
  

  2. USING关联删除（Teradata推荐写法）：

  WITH cte AS (
      SELECT 
          Id, 
          Proname, 
          Cityname, 
          Companyname,
          ItemsNo,
          ROW_NUMBER() OVER (
              PARTITION BY Id, Proname, Cityname, Companyname
              ORDER BY Id, Proname, Cityname, Companyname
          ) AS row_num
      FROM dba.tabdupes
  )
  DELETE FROM dba.tabdupes t
  USING cte
  WHERE t.Id = cte.Id
    AND t.Proname = cte.Proname
    AND t.Cityname = cte.Cityname
    AND t.Companyname = cte.Companyname
    AND t.ItemsNo = cte.ItemsNo
    AND cte.row_num > 1;
  

• 验证重复判断逻辑：检查PARTITION BY的字段是否符合你的重复定义。如果Id是表的主键/唯一标识，按Id分区会导致每个分组仅一条数据，永远不会出现row_num >1的情况。此时需要调整分区字段，比如改为按Proname, Cityname, Companyname, ItemsNo分区，确保重复数据会被分到同一组。

• 预测试CTE结果：先单独执行CTE的查询部分，确认是否存在row_num >1的记录，排查是否真的有重复数据：

  SELECT * FROM (
      SELECT 
          Id, 
          Proname, 
          Cityname, 
          Companyname,
          ItemsNo,
          ROW_NUMBER() OVER (
              PARTITION BY Id, Proname, Cityname, Companyname
              ORDER BY Id, Proname, Cityname, Companyname
          ) AS row_num
      FROM dba.tabdupes
  ) t WHERE row_num > 1;
  

• 确认权限与版本：确保当前用户拥有dba.tabdupes的DELETE权限；同时Teradata 14.10及以后版本才支持WITH子句配合DML操作，若版本过低需改用子查询写法。

内容的提问来源于stack exchange，提问作者Aurora