参见英文答案 > how can I delete duplicates in SQLite?                                    2个
>             Deleting duplicate rows from sqlite database                                    3个
我正在使用sqlite.我正在导入一个数据集,其中ID被外部分配到临时表中,然后将其插入到我的永久表中.永久表使用外部标识(RunId)并且没有其他标识列.

我将表从csv导入到新表Book1中,其中C15是ID列.然后我运行插入：

INSERT INTO PrimusRuns (RunId,TransientName,RunDateStart,RunType,TestDateStart,Gross,CPS,Shares,MaxExposure,PercentWin,Duration)
SELECT a.C15,a.C1,JULIANDAY(a.C2),a.C3,JULIANDAY(a.C4),a.C6,a.C8,a.C9,a.C10,a.C11,a.C14
FROM Book1 as a;

但是我得到一个主键约束错误：

[19] [sqlITE_CONSTRAINT_PRIMARYKEY] A PRIMARY KEY constraint Failed (UNIQUE constraint Failed: PrimusRuns.RunID)

首先,我认为其中一些行已经在表中但运行：

SELECT * FROM Book1 WHERE C15 IN(
  SELECT RunID from PrimusRuns
);

没有回报.

然后我意识到在运行时导入中有重复的行：

SELECT * FROM Book1 GROUP BY C15 HAVING COUNT(*) > 1

此聚合查询返回95行,这意味着我必须删除至少95行.如何删除告诉它删除重复项？

注意：还有其他类似的问题,但我的问题不同,因为id也是重复的.其他问题将列的其余部分分组并删除max(id).但在我的情况下,max id返回的行不仅仅是一行.

delete from 
  book1
  where id not in ( 
  select min(id)
  from Book1
  group by C15)

create table my_temp_distinct  as  
select col1,col2 ... 
from Book1
group by col1,col2,... 
having count(*)> 1

delete from 
  book1
  where id in ( 
  select min(id)
  from Book1
  group by C15)

insert into Book1 (col1,col2 ....)
select col1,...
from my_temp_distinct