Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 7 of 7 total
Thread Duplicate Records
Fri, Apr 22 2016 8:42 PMPermanent Link

John Postnikoff

I am wanting to delete duplicate records that have the same ZIP or Postal Code in a table I call ZONES.  I am using this method below and am wondering if there is a better way than the method below.


CREATE a temporatry table called ZONESTEMP


// This copies only one of each record into the temp talbe.
SELECT DISTINCT INTO ZONESTEMP FROM ZONES;

//Delete all the rows from the original table

DELETE FROM ZONES; or Use empty table

//Copy the distinct rows from the temporary table back into the original table that was emptied.

INSERT INTO ZONES
SELECT * FROM ZONESTEMP;

DROP TABLE ZONESTEMP;

//see the table without duplicates

SELECT * FROM ZONES;
Sat, Apr 23 2016 2:27 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

John


That's simple, easy to code, understandable and works. I'd stick with it

Roy Lambert
Sun, Apr 24 2016 11:18 PMPermanent Link

John Postnikoff

As per DBISAM manual, when I use INSERT  I found out that I lose all my indexes on the destination tables. Is there a way copy data and keep my indexes if I use INSERT, or do I have to re-create indexes?  

Thanks,

John


Roy Lambert wrote:

John


That's simple, easy to code, understandable and works. I'd stick with it

Roy Lambert
Mon, Apr 25 2016 4:07 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

John


Yes you'll need to recreate the indices. I'd take the opportunity to add a unique index to the table for zip or postal code to stop duplicates being entered in future.


Roy Lambert
Mon, Apr 25 2016 10:49 AMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

John,

<< As per DBISAM manual, when I use INSERT  I found out that I lose all my indexes on the destination tables. Is there a way copy data and keep my indexes if I use INSERT, or do I have to re-create indexes?   >>

Sure, use this instead:

INSERT INTO ZONESTEMP
SELECT DISTINCT * FROM ZONES;

Tim Young
Elevate Software
www.elevatesoft.com
Mon, Apr 25 2016 11:26 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

Tim

><< As per DBISAM manual, when I use INSERT I found out that I lose all my indexes on the destination tables. Is there a way copy data and keep my indexes if I use INSERT, or do I have to re-create indexes? >>
>
>Sure, use this instead:
>
>INSERT INTO ZONESTEMP
>SELECT DISTINCT * FROM ZONES;

Is that going to work ie will it create the necessary indices? I don't think John cares if there are indices on ZONETEMP at all since he's just using it as a temporary holder for the deduplicated rows. But posting to your reply makes me wonder just what's going on.

SELECT DISTINCT INTO ZONESTEMP FROM ZONES;
DELETE FROM ZONES;
INSERT INTO ZONES SELECT * FROM ZONESTEMP;
DROP TABLE ZONESTEMP;

Should have ended up with ZONES having the deduplicated data with its indices intact since nothing was done to remove them.

If there's much in ZONES I'd also go with EMPTY TABLE ZONES rather than DELETE FROM ZONES

Roy Lambert
Tue, Apr 26 2016 12:01 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Roy,

<< Is that going to work ie will it create the necessary indices? >>

What indexes are you referring to ?  When using INSERT/DELETE, as opposed to INTO, you're never touching any indexes, just moving records around.

<< I don't think John cares if there are indices on ZONETEMP at all since he's just using it as a temporary holder for the deduplicated rows. >>

Yeah, I went back and read his reply again, and something's not right.  He won't lose any indexes when using INSERT (which I read as INTO originally, hence my reply).

<< SELECT DISTINCT INTO ZONESTEMP FROM ZONES;
DELETE FROM ZONES;
INSERT INTO ZONES SELECT * FROM ZONESTEMP;
DROP TABLE ZONESTEMP;

Should have ended up with ZONES having the deduplicated data with its indices intact since nothing was done to remove them. >>

Yep, that is 100% correct.

Tim Young
Elevate Software
www.elevatesoft.com
Image