473,246 Members | 3,285 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,246 software developers and data experts.

Delete duplicates in each column, not duplicate records

watertraveller
Hi all. My ultimate goal is to return two columns, where no single value appears anywhere twice. This means that not only do I want to check that nothing from column A appears in column B and vice-versa, but I also don't want the same value appearing twice in A and twice in B.

So far I have:

Expand|Select|Wrap|Line Numbers
  1. --Diff the columns
  2. INSERT INTO @Table
  3. SELECT One, Two
  4. FROM @Column1 a
  5. FULL OUTER JOIN @Column2 b
  6. ON ((One NOT IN (Two)) OR (Two NOT IN (One)))
  7.  
But then my trouble is removing the redundant values in each column. I tried splitting the table into single-column tables so that they would no longer be linked together, and then deleting duplicate records out of that single-column table. But it didn't work.

Expand|Select|Wrap|Line Numbers
  1. -- Attempt at removing duplicate values
  2. DELETE FROM @Column1
  3. DELETE FROM @Column2
  4. INSERT INTO @Column1 SELECT One FROM @Table
  5. INSERT INTO @Column2 SELECT Two FROM @Table
  6. INSERT INTO @Column3 SELECT DISTINCT One FROM @Column1 
  7.          ORDER BY One
  8. INSERT INTO @Column4 SELECT DISTINCT Two FROM @Column2 
  9.          ORDER BY Two
  10.  
But when I display these two columns together there are duplicates, and when I display them individually they aren't even in order. What's wrong here?

Thanks,
-C
Oct 17 '08 #1
1 4070
ck9663
2,878 Expert 2GB
Could you post some of your sample data? Let's see if we can find a pattern among the values.

-- CK
Oct 17 '08 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Patrizio | last post by:
Hi All, I've the following table with a PK defined on an IDENTITY column (INSERT_SEQ): CREATE TABLE MYDATA ( MID NUMERIC(19,0) NOT NULL, MYVALUE FLOAT NOT NULL, TIMEKEY ...
3
by: Alexander Anderson | last post by:
I have a DELETE statement that deletes duplicate data from a table. It takes a long time to execute, so I thought I'd seek advice here. The structure of the table is little funny. The following is...
2
by: ms | last post by:
Access 2000: I am trying to delete duplicate records imported to a staging table leaving one of the duplicates to be imported into the live table. A unique record is based on a composite key of 3...
1
by: MHenry | last post by:
Hi, I have a table with duplicate records. Some of the duplicates need to be eliminated from the table and some need not. A duplicate record does not need to be eliminated if the one record...
4
by: KT | last post by:
Is there any one click solution that would do the trick? I would like to create a button, so the person who maintains the database can perform clean up work to delete duplicate records which...
1
by: aknoch | last post by:
My basic situation is this - I ONLY want duplicates, so the opposite of DISTINCT: I have two tables. Ordinarily, Table1ColumnA corresponds in a one to one ratio with Table2ColumnB through a...
7
by: AccessHunter | last post by:
I have a access query that has more than 400000 records including duplicates.First I need help in finding a way to filter the whole 400000 records and list all duplicates. Lets say ID, Job &...
7
by: jmstur2 | last post by:
I have a table with what I consider duplicate records. Data in all columns are duplicate except for the date column, meaning that duplicate data was entered on different dates and those dates were...
2
by: farouqdin | last post by:
Hi all i have code which loops through table and deletes the duplicate records. This code does it for one table. How do i change it so it goes through several tables? On Error Resume Next Dim...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.