Delete row in SQLLite Database with python where with two conditions

  python, sqlite

I’m new to python and SQLLite and I hope you can help me. I’m trying to figure out how to delete a row in a SQLLite database, for what I have two conditions.

This is roughly how my code looks:
a.execute("""Create Table xxx (
Data,
ID,
Date)""")

a.execute("Insert into xxx values (?, ?, ?)", (data, ID , date)""")

So my problem is, that a lot of data is going into the database and the ID is not distinct. If there are two ore more datasets with the same ID, I want to only keep the one with the latest date)

So I tried it with this code:
a.execute ("""delete from xxx
where date not in(
Select max(date) from xxx
group by ID

But the problem is, it doesn’t delete anything, probably because every date is in there.

Can you please help me?

Source: Python Questions

LEAVE A COMMENT