Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 7 of 7 total |
Duplicate Records |
Fri, Apr 22 2016 8:42 PM | Permanent 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 AM | Permanent Link |
Roy Lambert NLH Associates 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 PM | Permanent 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 AM | Permanent Link |
Roy Lambert NLH Associates 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 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. 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 AM | Permanent Link |
Roy Lambert NLH Associates 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 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. 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 |
This web page was last updated on Thursday, March 28, 2024 at 06:05 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |