CODEWITHSUNDEEP
sqlplsqloracle10g
03Usr
03Usr

Reputation: 3435

How to compare tables and find duplicates and also find columns with different value

I have the following tables in Oracle 10g:

Table1
Name  Status
a         closed
b         live
c         live

Table2
Name  Status
a         final
b         live
c         live

There are no primary keys in both tables, and I am trying to write a query which will return identical rows without looping both tables and comparing rows/columns. If the status column is different then the row in the Table2 takes presedence.

So in the above example my query should return this:

Name   Status
a         final
b         live
c         live

Upvotes: 0

Views: 94

Answers (2)

Anthony Grist
Anthony Grist

Reputation: 38345

Something like this?

SELECT table1.Name, table2.Status
FROM table1
INNER JOIN table2 ON table1.Name = table2.Name

By always returning table2.Status you've covered both the case when they're the same and when they're different (essentially it doesn't matter what the value of table1.Status is).

Upvotes: 1

John Woo
John Woo

Reputation: 263723

Since you have mentioned that there are no Primary Key on both tables, I'm assuming that there maybe a possibility that a row may exist on Table1, Table2, or both. The query below uses Common Table Expression and Windowing function to get such result.

WITH unionTable
AS
(
    SELECT  Name, Status, 1 AS ordr FROM Table1
    UNION 
    SELECT  Name, Status, 2 AS ordr FROM Table2
),
ranks 
AS
(
    SELECT  Name, Status,
            ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY ordr DESC) rn
    FROM    unionTable
)
SELECT  Name, Status
FROM    ranks
WHERE   rn = 1

Upvotes: 3

Related Questions