thread184-685216 - remove duplicate entries in a table 2

[keepingbusy]

thread184-685216

This thread enquires about removing duplicating entries, but I cant see anywhere how you actually remove the entry. I'm trying to search for any duplicate entries in a DBF that has about 380,000 records, find the duplicate, then delete it from that same table. We are only concerned with one field called TITLE which is a character field (60)

Any suggestions please?

VisFox Version 6

Many thanks
Lee

Alone we can do so little, together we can do so much

 

[ramani]

HI

Before saying DELETE all duplicates on a single field, you have to decide, which of the entries you want to delete.
When there are 3 records, let us say,.. which of the three you want to delete. SO this is a complex question, you have to decide.

We can make a selection..

SELECT title FROM myTable GROUP BY title ;
HAVING COUNT(title) > 1

An example to Delete whichever one the system want to delete.. (funny though)

DELETE ALL FOR ;
RECNO() NOT IN ;
(SELECT RECNO("myTable&quot AS recno ;
FROM myTable GROUP BY title)



____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

When you ask VFP questions, please add VFP version.

 

[jimstarr]

Hi Lee,

SELECT * FROM mytable INTO CURSOR mycursor GROUP BY TITLE
SELECT mytable
ZAP
APPEND FROM DBF('mycursor')

or to delete in place:

CREATE CURSOR mycursor (title c(60))
INDEX ON title TAG title
SELECT mytable
SCAN
IF SEEK(mytable.title, 'mycursor')
DELETE
ELSE
INSERT INTO mycursor VALUE(mytable.title)
ENDIF
ENDSCAN


Jim

 

[keepingbusy]

Hi Ji, Ramani

Thanks to both of you for responding so quickly. I've resolved our problem by using Jim's code shown below and just changing the variables to suit.

By the way, nothing wrong with Ramani's code, just tried Jim's first and they say "Don't fix something that's not broken!"

Here's the change for anyone else interested and just a reminder we are using VisFox Version 6

CREATE CURSOR mycursor (title c(60))
INDEX ON title TAG title
SELECT NEWFILE
SCAN
IF SEEK(newfile.title, 'mycursor')
DELETE
ELSE
INSERT INTO mycursor VALUE(newfile.title)
ENDIF
ENDSCAN

Kindest regards
Lee......

Alone we can do so little, together we can do so much

 

[craigsboyd]

ramani,

Star for you. I thought about how I could do this with a single delete statement and, though I knew it was possible, I could not think of the solution you came up with. I find your solution to be very good and I like that it is not dependent on an index being present.

Slighthaze = NULL
[sub]craig1442@mchsi.com[/sub][sup]

"Whom computers would destroy, they must first drive mad." - Anon​

[/sup]

 

[ramani]

Craig, Thanks for the star.

____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

When you ask VFP questions, please add VFP version.

 

[jimstarr]

Ramani, I don't think FoxPro will allow

"RECNO() NOT IN "

Jim

 

[keepingbusy]

Ramani

I'm no expert, but Jim is right. I tried the code and there was an error message.

Lee

Alone we can do so little, together we can do so much

 

[ramani]

Oh ho.... I am sorry. Jim is right Lee. I was traped and the syntax was beliavably correct that Craig put a star for me.

Now I have to make amends by putting some nice code for this thread. Let me come back soon.

And Lee go with something working as you said. No need to change unless that has some concern for you. And you owe a star to Jim. If you had not given, when I come back, I will do that. Jim deserves one irrespective of what code I come out with.

This is Rush Hour now and me a Fall Guy!



____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

When you ask VFP questions, please add VFP version.

 

[keepingbusy]

Hi Ramani

Thank you for that, and yes Jim does deserve as a star! (Thanks Jim)

Lee

Alone we can do so little, together we can do so much

 

[PaulColes]

SELECT DISTINCT * from thingy

would work

 

[PaulColes]

ooops - next time I'll read all the question

 

[danceman]

delete from tablename where title in (select title from tablename group by title having count(title) > 1)

I have used this with a different field. It was to remove duplicate address.

Attitude is Everything

 

[keepingbusy]

Hi all

We have now resolved our problem using the following code, part of which was posted in this thread. If it helps anyone, here it is (We are using VFP6):

SELECT * FROM NEWFILE INTO CURSOR mycursor GROUP BY TITLE
SELECT NEWFILE
ZAP
APPEND FROM DBF('mycursor')
USE NEWFILE EXCL
PACK

It works perfect for removing any duplicate entries in a DBF so thanks again to all those assisted and responded

Kindest regards
Lee

Alone we can do so little, together we can do so much