Duplicate Records 1

[whatcom]

Hi.

I have a table (called TAB1), that has just two columns (New#, Old#). The purpose of this table is to associate old fee numbers from 2008 with the new fee numbers for 2009.

For some reason, this table got a bit messed up, and some of the old numbers are duplicated. A sample of the table might look something like:

New# Old#
1000 1000
2000 1000
2001 1001
1002 1002
2002 1002
2003 2003

What I am trying to do is write a query that removes all the records where New# = Old# EXCEPT when there is only one distinct "old" record. In that instance (and only in that type of instance), the record is valid.

Using the above table, I would want the result to do something like:

New# Old#
1000 1000 (remove)
2000 1000 (keep)
2001 1001 (keep)
1002 1002 (remove)
2002 1002 (keep)
2003 2003 (keep)

Any ideas?

 

[RiverGuy]

DELETE a
FROM TAB1 a
INNER JOIN TAB1 b
ON a.Old = b.Old AND a.New <> b.New
WHERE (a.New = a.Old)

Remember to always test these types of queries on a test copy of the table first, before you're satisfied it meets your requirements.

 

[whatcom]

Ok - I'll give it a shot. Thank you so much.