?
Solved

I need to eliminate duplicate rows in SQLITE

Posted on 2011-10-24
4
Medium Priority
?
340 Views
Last Modified: 2012-08-14
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
Comment
Question by:dolamitejenkins
  • 2
  • 2
4 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37018423
See if the following link has the SQL you need:
http://stackoverflow.com/questions/4651936/sql-remove-duplicate-pairs
0
 

Author Comment

by:dolamitejenkins
ID: 37020484
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
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 37020526
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
 

Author Closing Comment

by:dolamitejenkins
ID: 37022016
Thank you
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
In this article, we’ll look at how to deploy ProxySQL.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

755 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question