Delete multiple rows and keep only one for each id

Question

I have table that looks like this that has two columns:

id
name

I have multiple records and some of them has the same id, I want to delete all the duplicates and only keep one id for each row.

So I have the data like this:

id: 1
name: Test1

id: 1
name: Test1

id: 2
name: Test1

id: 2
name: Test1

id: 2
name: Test1

id: 3
name: Test1

And want it to be like this instead:

id: 1
name: Test1

id: 2
name: Test1

id: 3
name: Test1

Any ideas how to achieve this? I have tried this answer but it returned all rows with id 1...

Find an answer here http://stackoverflow.com/questions/8190541/deleting-duplicate-rows-from-sqlite-database#8190671 and fix your primary key so that it doesn't happen again. — Rolf of Saxony, Apr 27 '17 at 15:14
@RolfofSaxony, no it´s not a duplicate that answer returned all id´s with 1 and deleted the rest... — , Apr 27 '17 at 15:16
@RolfofSaxony and in that answer he compares two columns I only compare one. So could you please remove the duplicate? — , Apr 27 '17 at 15:20

mechanical_meat · Accepted Answer · 2017-04-27T16:40:00.133

2

I can see it working by making another table and copying into that table just the rows you want. This is a tested solution.

CREATE TABLE mytable (id,name);

INSERT INTO mytable
    (`id`, `name`)
VALUES
    ('1','Test1'),
    ('1','Test1'),
    ('2','Test1'),
    ('2','Test1'),
    ('2','Test1'),
    ('3','Test1')

CREATE TABLE mytable2 (id,name);

INSERT INTO mytable2 
    select m2.id, m2.name 
    from mytable m2
    group by m2.id || m2.name
    having count(m2.id || m2.name) >= 1

Desired resultset:

>>> conn.execute('select * from mytable2;').fetchall()
[(u'1', u'Test1'), (u'2', u'Test1'), (u'3', u'Test1')]

edited Apr 27 '17 at 16:40

answered Apr 27 '17 at 15:03

mechanical_meat

163,903
24
228
223

Thanks for your reply, but this only keps all id´s with id: 1 and deleted the rest. – Apr 27 '17 at 15:05
Change `group by name` to `group by id` – redent84 Apr 27 '17 at 15:05
@MikeSpenz: I've now provided a different solution. Let me know how it works for you. – mechanical_meat Apr 27 '17 at 16:41

score 1 · Answer 2 · answered Apr 27 '17 at 17:02

1

Assuming that you want to delete the duplicates from database, not only from output.

delete from mytable where ROWID not in (select ROWID from mytable group by id);

select * from mytable;

Output:

1|Test1
2|Test1
3|Test1

make a table on the fly, of rowids of only one occurrence of each id
(the part within ())
delete everything which does not have one of those rowids
select everything which remains, just for demonstration

Using SQLite 3.18.0 2017-03-28 18:48:43

Thanks to @bernie for making a mcve!
It is so much more efficient and more fun to work on a mcve than on a list of values.

answered Apr 27 '17 at 17:02

Yunnosch

26,130
9
42
54

As suggested, this does look suspiciously like the accepted answer for http://stackoverflow.com/questions/8190541/deleting-duplicate-rows-from-sqlite-database#8190671 Still +1 for proving it – Rolf of Saxony Apr 28 '17 at 15:39
@RolfofSaxony Yes and no. Yes: technically same. No: I did not get the impression that the answer was visible from point of view of OP (even with the help in a comment here) and decided to skin-tailor an answer anyway (and did so without actually looking there). Nevertheless: Credits and thanks to SO-authors of earlier comments and answers with the ROWID trick, which definitly is where I learned it. And to you for the upvote. ;-) – Yunnosch Apr 28 '17 at 18:08

Delete multiple rows and keep only one for each id

2 Answers2