Thanks in advance for any assistance.

I have a table that is added to daily and due to some bad records coming from vendor, I end up with duplicate records that are not perfectly alike.

SAMPLE DATA:
customer_number field2 field3 field4
12345 abc 123 abc
12345 abc NULL NULL
6789 def 456 hij
6789 def NULL NULL
11111 wxyz NULL NULL

I need to eliminate records with duplicate customer_number and null in any field. But I want to leave non-duplicate records with null in any field. The database holds many years of monthly data so there will be duplicate customer numbers, but I only want to keep duplicates with no nulls. I can't delete records with nulls (if not duplicate) and I can't jus delete dups w/o nulls.

thanks again,

do you have a pK on this table?

If you have a primary key try this example below. If you don't add one. Also see the second example.

SELECT DISTINCT *
INTO duplicate_table
FROM original_table
GROUP BY key_value
HAVING COUNT(key_value) > 1

DELETE original_table
WHERE key_value
IN (SELECT key_value
FROM duplicate_table)

INSERT original_table
SELECT *
FROM duplicate_table

DROP TABLE duplicate_table

   SELECT DISTINCT *
      INTO duplicate_table
      FROM original_table
      GROUP BY key_value
      HAVING COUNT(key_value) > 1

   DELETE original_table
      WHERE key_value
      IN (SELECT key_value
             FROM duplicate_table)

   INSERT original_table
      SELECT *
         FROM duplicate_table

     DROP TABLE duplicate_table


When executed, this script moves one instance of any duplicate row in the original table to a duplicate table. It then deletes all rows from the original table that also reside in the duplicate table. Next, the rows in the duplicate table are moved back into the original table. Finally, the duplicate table is dropped.


Example 2

SELECT *
FROM Customer
WHERE Customer_number = (SELECT TOP 1 Customer_number
FROM Customer t1
WHERE t1.field2 = Customer.col2
ORDER BY Customer_number DESC )-- Or ASC

Your data
SAMPLE DATA:
customer_number field2 field3 field4
12345 abc 123 abc
12345 abc NULL NULL
6789 def 456 hij
6789 def NULL NULL
11111 wxyz NULL NULL

excellent thank you,