• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 372
  • Last Modified:

I need to eliminate duplicate rows in SQLITE

I have a db that is collecting data from other data bases and I need to eliminate duplicate rows in the main data base , but it has to be on at least 3 criteria id, first name and last name... because I would use insert or replace but it is conceivable that there would be duplicate id entries but not likely that duplicate id, first name , last name entries... how would I do that in SQLITE ?
0
dolamitejenkins
Asked:
dolamitejenkins
  • 2
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
See if the following link has the SQL you need:
http://stackoverflow.com/questions/4651936/sql-remove-duplicate-pairs
0
 
dolamitejenkinsAuthor Commented:
not exactly sure i understand that post... i am a newbie

Traceback (most recent call last):
  File "C:\Python26\lib\threading.py", line 532, in __bootstrap_inner
    self.run()
  File "c:\Python26\sign\OCTOBER24.py", line 6754, in run
    self.repopulate()
  File "c:\Python26\sign\OCTOBER24.py", line 6757, in repopulate
    self.findduplicates(self)
  File "c:\Python26\sign\OCTOBER24.py", line 6761, in findduplicates
    cur.execute('DELETE FROM ptrecords A WHERE EXISTS (SELECT * FROM ptrecords B WHERE A.Incident_number = B.Incident_number AND A.Last_Name, = B.Last_Name, AND A.First_Name=B.First_Name')
OperationalError: near "A": syntax error

Open in new window


con = lite.connect('specialeventms2011a.sqlite')
	cur = con.cursor()
	cur.execute('DELETE FROM ptrecords A WHERE EXISTS (SELECT * FROM ptrecords B WHERE A.Incident_number = B.Incident_number AND A.Last_Name, = B.Last_Name, AND A.First_Name=B.First_Name')
	con.commit()
	cur.close()
	con.close()

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
you appear to have some commas where they don't belong.

Try:


con = lite.connect('specialeventms2011a.sqlite')
	cur = con.cursor()
	cur.execute('DELETE FROM ptrecords A WHERE EXISTS (SELECT * FROM ptrecords B WHERE A.Incident_number = B.Incident_number AND A.Last_Name = B.Last_Name AND A.First_Name=B.First_Name')
	con.commit()
	cur.close()
	con.close()

Open in new window

0
 
dolamitejenkinsAuthor Commented:
Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now