派生表不允许删除

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 one。

我的查询-使用这个我可以看到重复的

 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 table驱动程序语法错误:派生表不允许删除。

我的问题-

我是否可以在不使用派生表的情况下删除这些dupe，或者是否有任何其他方法可以不使用派生表来转换当前查询？

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)