De-Duplicating Records

Startbeitrag von David Egan am 21.01.2016 05:19

Hi
We've just imported a heap of location records from an old DBase file into HFSQL & there are a lot of duplicates. I thought the easiest way to do this would be a quick SQL script in the control centre. The following works & returns the records which should be deleted

SELECT * FROM LOCATION
WHERE LocationId NOT IN
(SELECT MIN(LocationID) FROM LOCATION
GROUP BY POSTCODE,DESCRIPTION,LATITUDE,LONGITUDE)

However if I replace SELECT * with DELETE I get an error "No analysis opened: LOCATION file not described". Doesn't make any sense to me as it's all in the HF Control Centre anyway! I can easily write a little routine to do the de-duplication another way but what I thought would be a 5 minute job has certainly not turned out that way.
Any thoughts?

Thanks

David

Antworten:

Without testing or checking, it sounds like the HFSQL DELETE function can not parse sub-selects. In the old days, I would select into a temp table and use the ids in the temp table to delete from LOCATION.

von DerekM - am 22.01.2016 06:45
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.