python sqlite3 DELETE FROM table row if exists

Question

I have a python script that inserts rows into a table. I want it to either delete duplicate rows or ignore the INSERT if the row exists. What I have tried so far:

#delete duplicate entries
c.execute('''DELETE FROM server WHERE sites NOT IN
            (SELECT MIN(sites) sites FROM server GROUP BY sites)''')

The table "server" is a single column "sites". This is a table of web sites. It's not throwing any errors. It's just doesn't not deleting the duplicates.

CL. · Accepted Answer · 2017-04-07 14:07:21Z

1

Comparing against the sites itself does not help, because the duplicates have the same value in this column.

You have to use some other column that is unique. In this case, you could use the rowid:

DELETE FROM server
WHERE rowid NOT IN (SELECT min(rowid)
                    FROM server
                    GROUP BY sites);

answered Apr 7, 2017 at 14:07

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user5780947 Over a year ago

That was it. Thanks.

Collectives™ on Stack Overflow

python sqlite3 DELETE FROM table row if exists

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related