Match field values and delete record

[Damian]

Hi,

I'm trying to identify records in a table where the value in one field
matches the value in another field (but different record) and then delete one
of those two records.

Just for the exercise lets say my table is called "MASTERTABLE" and my two
fields are called "FIELD_A" and "FIELD_B"

Can anyone help please?

Thanks D

 

[Allen Browne]

Use a subquery to identify the duplicates.

This example assumes a primary key field named ID:

DELETE FROM MasterTable
WHERE ID <>
(SELECT Min(ID) AS MinOfID
FROM MasterTable AS Dupe
WHERE Dupe.Field_B = MasterTable.Field_A);

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

 

[Damian]

Thank you for your response Allen, unfortunately however when I run the query
it deletes every record in my table. I think perhaps it is my fault however
for not explaining my original problem properly and further clarification is
required...

Hers a tiny sub-set of my data to give you a better idea... in this case I
would like to delete records with ID 2003, 2005, 2006 (or vice versa). Would
I still use your original SQL?

ID FIELD_A FIELD_B
2001 1490422 2490421
2002 1490423 3490421
2003 2490421 1490422
2004 2490423 3490422
2005 3490421 1490423
2006 3490422 2490423


Cheers, Damian