如何删除子查询返回的所有记录？

Question

我希望删除由某个查询返回的所有记录，但我无法找到一个合适的方法来做到这一点。我尝试过 DELETE FROM mytable WHERE EXISTS (subquery)，但是它删除了表中的所有记录而不仅仅是子查询选定的记录。

我的子查询看起来像这样：

SELECT 
MAX(columnA) as columnA,
-- 50 other columns
FROM myTable 
GROUP BY
-- the 50 other columns above
having count(*) > 1;

这应该很容易，但是我现在脑子一片空白。感谢任何建议。

编辑：columnA不是唯一的（这个表中也没有其他列是全局唯一的）。

- daZza

---

你需要一个相关子查询。 - jarlh

Answer 1

假设您想使用 in:

DELETE FROM myTable
    WHERE columnA IN (SELECT MAX(columnA) as columnA
                      FROM myTable 
                      GROUP BY -- the 50 other columns above 
                      HAVING count(*) > 1
                     );

假定columnA在表中是全局唯一的。否则，你需要更加努力去解决问题。

DELETE FROM myTable t
    WHERE EXISTS (SELECT 1
                  FROM (SELECT MAX(columnA) as columnA,
                               col1, col2, . . .
                        FROM myTable 
                        GROUP BY -- the 50 other columns above 
                        HAVING count(*) > 1
                       ) t2
                  WHERE t.columnA = t2.columnA AND
                        t.col1 = t2.col1 AND
                        t.col2 = t2.col2 AND . . .
                 );

即使如此，如果任何一列具有NULL值，则不能保证此方法有效（尽管可以轻松修改条件以处理此情况）。

- Gordon Linoff

---

这不是唯一的问题 :) 只有通过聚合所有50列并在columnA上使用MAX()才能实现唯一性。第二个解决方案看起来很有趣，我会尝试一下。 - daZza

Answer 2

如果唯一性只由一组列保证，另一个解决方案是：

delete table1 where (col1, col2, ...) in (
    select min(col1), col2, ...
    from table1 
    where...
    group by col2, ...
)

null值将被忽略而不会被删除。

为了实现这一点，请尝试类似以下的操作：

with data (id, val1, val2) as 
(
select 1, '10', 10 from dual union all
select 2, '20', 21 from dual union all
select 2, null, 21 from dual union all
select 2, '20', null from dual 
)
-- map null values in column to a nonexistent value in this column
select * from data d where (d.id, nvl(d.val1, '#<null>')) in 
(select dd.id, nvl(dd.val1, '#<null>') from data dd)

- Frank Ockenfuss

Answer 3

如果您需要删除表中所有行，使得给定字段的值在查询结果中，您可以使用类似以下的代码：

delete table
my column in ( select column from ...)

- Aleksej

---

只有在列是唯一值的情况下才能起作用，但不幸的是，在我的情况下它不是唯一的。我能够获得唯一记录的唯一方法是聚合所有50个列并在columnA上使用MAX()函数。 - daZza