delete reverse duplicates

[ggidder]

Hi,
I have a table with related values. Sometimes there are records with the reverse relation. I want to delete those but dont know how to handle this. eg:
table with two columns :
A-B
A-C
A-D
A-E
B-C
B-E
F-G
C-A
C-D

In the example above C-A is the record I want deleted because it is the reverse record of A-C already in the list.
How can I do this using access Queries ?

Thanx

[ggidder]

ok,i think this does it..

SELECT E1, E2
FROM Table1
WHERE E1<=E2
UNION
SELECT E2, E1
FROM Table1
WHERE E1>E2

[NeoPa]

I doubt it would actually. I'm not sure it even, reliably, returns the records you want without those items you've described.

A DELETE query has to work on an updatable data source. Once UNION is used in Jet/ACE the data source becomes non-updatable.

I assume that your data is actually stored as two separate fields ([E1] & [E2]), rather than a string as originally shown. If so then the following should probably work :

DELETE [tA].*
FROM   [TableName] AS [tA]
       INNER JOIN
       [TableName] AS [tB]
  ON   ([tA].[E1]=[tB].[E2])
 AND   ([tA].[E2]=[tB].[E1])
WHERE  ([tA].[E2]>[tA].[E1])

[ggidder]

Hi NeoPa,


It does the trick for me when I checked on a small amount of data. It leaeves out the reverse duplicates and the records where both columns wouldhave the same value. the order of the data in the records gets changed with the lower values ending up in the first column but that is just fine making it easier to compare withanother table 'sorted' the same way. I guess it is reliable as long as no two ifferent strings can produce the same value when compared.

As for your much appreciated answer I d like to try it too. But I dont seem to get it to work... I understand [TableName] is the name of my table [Table1] in this case, but do I have to replace the tA tB also by some value, suppose not but... can't get it to work (btw using acces 2010 and yes the data is in seperate fields I just just used '-' to difference between column E1 en E2.

Greetings,

[NeoPa]

That makes sense. I'm afraid I did it from memory and had forgotten how fussy Access's Jet/ACE can be when handling DELETE queries. My bad.

As a general rule, deleting must be done on a single table. Often this is done by updating something in the record first, with an UPDATE query that allows more complicated SQL, followed by a separate query that deletes the records that have been so flagged. That could be done in this case.

An alternative, if the table has a PK (or any unique index for that matter) defined, then a subquery can be used that returns the records to be deleted in the WHERE clause of a simpler DELETE query. This one I actually tested first and it works fine (After adding a PK [ID] to the table of course) :

DELETE
FROM   [Table1]
WHERE  ([ID] In(SELECT [tA].[ID]
                FROM   [Table1] AS [tA]
                       INNER JOIN
                       [Table1] AS [tB]
                  ON   ([tA].[E1]=[tB].[E2])
                 AND   ([tA].[E2]=[tB].[E1])
                WHERE  ([tA].[E2]<[tA].[E1])))