SQLTeam.com | Weblogs | Forums

Delete partial duplicates


#1

I have two tables that have an identical structure in the same database. I created a view with a UNION query to combine the two tables. The problem is 3 out of the 60+ columns have values in one table and no values in the other so UNION and UNION ALL return the same set of data. So I am left with 2 rows similar to what you see below and I need to exclude the ones that are NULL. The problem is there are other records in the 1.3M records that are NULL and should be included. So I want to exclude the row where those three columns are NULL if there is another row where Col1 is duplicated.

Col1 Col2 Col3 Col4 col5
123 Bob NULL NULL NULL
123 Bob 5432 1234 9876

Col1 Col2 Col3 Col4 Col5
123 Bob 5432 1234 9876


#2

Dunno if this is the answer?

SELECT Col1, Col2, ...
FROM Table1
UNION ALL
SELECT Col1, Col2, ...
FROM Table2 AS T2
WHERE NOT EXIST
(
    SELECT *
    FROM Table1 AS T1
    WHERE     T1.PKey1 = T2.PKey1
          AND T1.PKey2 = T2.PKey2
          AND ...
)

The PKey list of columns would need to be extended to include all the columns required to determine a "difference"