我们可以在不使用中间表的情况下从teradata中的表中删除重复记录吗
问题描述：
我们能否在不使用中间表的情况下从 teradata 的多集表中删除重复记录.
Can we delete duplicate records from a multiset table in teradata without using intermediate table.
假设我们有 2 行有值1、2、3和 1, 2, 3在我的多集表中然后删除后我应该有只有一行，即 1、2、3.
Suppose we have 2 rows with values 1, 2, 3 and 1, 2, 3 in my multiset table then after delete i should have only one row i.e. 1, 2, 3.
答
除非在您的系统上启用了 ROWID 使用(并且概率非常低)，否则您不能这样做.您可以通过尝试解释一个 SELECT ROWID FROM 表来轻松测试它；
You can't unless the ROWID usage has been enabled on your system (and probablity is quite low). You can easily test it by trying to explain a SELECT ROWID FROM table;
否则有两种可能的方式.
Otherwise there are two possible ways.
重复次数少:
- 创建一个新表作为
SELECT all columns FROM table GROUP BY all columns HAVING COUNT(*) >1; DELETE FROM tab WHERE EXISTS (SELECT * FROM newtab WHERE...)INSERT INTO tab SELECT * FROM newtab
大量重复:
- 使用
SELECT DISTINCT *复制到新表或复制到 SET TABLE 以去除重复项，然后重新插入
- copy to a new table using
SELECT DISTINCT *or copy to a SET TABLE to get rid of the duplicates and then re-INSERT back