不允许删除派生表

Question

我的数据看起来像 -

+-------+------------+------------+
|  ID   | START_DATE |  END_DATE  |
+-------+------------+------------+
| 12345 | 11/09/2008 | 02/02/2011 |
| 12345 | 11/09/2008 | 11/09/2008 |
| 12345 | 02/02/2011 | 12/31/9999 |
+-------+------------+------------+

我想要它如何——

+-------+------------+------------+
|  ID   | START_DATE |  END_DATE  |
+-------+------------+------------+
| 12345 | 11/09/2008 | 02/02/2011 |
| 12345 | 02/02/2011 | 12/31/9999 |
+-------+------------+------------+

基本上我正在删除列 ID 和 START_DATE 下的重复项，并保留最新的END_DATE

我的查询 -使用它我可以看到重复项

 SELECT * FROM (SELECT ID,START_DATE,END_DATE,
   ROW_NUMBER() OVER (PARTITION BY ID, START_DATE order by END_DATE desc) RN
 FROM MYTABLE) a WHERE a.RN>1

上面的 SELECT 查询工作得很好，但是当我尝试使用相同的查询删除时，如下所示 -

 DELETE FROM (SELECT ID,START_DATE,END_DATE,
   ROW_NUMBER() OVER (PARTITION BY ID, START_DATE order by END_DATE desc) RN
 FROM MYTABLE) a WHERE a.RN>1

我收到提示 -

错误 [42000] [Teradata][ODBC Teradata 驱动程序][Teradata 数据库] 语法错误：不允许删除派生表。

我的要求——

有没有办法在不进入派生表的情况下删除这些重复，或者在不使用派生表的情况下转换当前查询的任何其他方法

Answer 1

如果你在(id, start_date, end_date)上没有重复项，那么你可以这样做：

delete from mytable t
    where t.end_date < (select max(t2.end_date)
                        from mytable t2
                        where t2.id = tid and t2.start_date = t.start_date
                       );

Answer 2

您可以删除第一个查询选择的记录并使用其 ID：

Delete from MYTABLE WHERE ID IN (
 SELECT ID FROM (SELECT ID,START_DATE,END_DATE,
   ROW_NUMBER() OVER (PARTITION BY ID, START_DATE order by END_DATE desc) RN
 FROM MYTABLE) a WHERE a.RN>1)