identifying reversing data (1 Viewer)

[phamyh]

I have 2 columns that have 2 set of different numbers. But column one has column 2's data also. how can i identify them and delete them from the table.

for example;

column 1 column 2
12345 67891
67891 12345

I want to identify the conbinnation of [67891 12345] which is a repeat in reverse and remove them.

What's the best way to do this?

Thanks!

 

[ajetrumpet]

I have 2 columns that have 2 set of different numbers. But column one has column 2's data also. how can i identify them and delete them from the table.

for example;

column 1 column 2
12345 67891
67891 12345

I want to identify the conbinnation of [67891 12345] which is a repeat in reverse and remove them.

What's the best way to do this?

Thanks!

interesting question. something i never seen before. backup your source table and then try running this query:

DELETE * FROM TABLE
   WHERE ([field2] & " " & [field1] IN (
      SELECT ([field2] & " " & [field1]) FROM TABLE);

i have a sneaking suspicioun that this code will delete all of your records and not just the reversed dups, but im' too tired to think why it might. backup your table b4 you try this...

 

[Atomic Shrimp]

Yes, I think it will delete both records of the pair - because as far as the query is concerned, neither record is the 'right' one and the other wrong - they're both simply different from each other in exactly the same way.

I can't remember an elegant way around this at the moment either - although I know there is one, because I have done this sort of deduplication myself in the past.