CODEWITHSUNDEEP
sqloracle-databaseplsqloracle10g
rs.
rs.

Reputation: 27427

Oracle - difference or changes between two rows from two tables

I have two tables both have same schema, one will have previous day records other will have current. I want to compare both and find only changes or only rows that have different value in atleast one column.

How is this possible in pl/sql, oracle? (I did code something similar using checksum in T-SQL but not sure how to do in pl/sql)

Upvotes: 4

Views: 30705

Answers (3)

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

As Dougman posted one of the most efficient way to compare two data sets is to GROUP them. I usually use a query like this to compare two tables:

SELECT MIN(which), COUNT(*), pk, col1, col2, col3, col4
  FROM (SELECT 'old data' which, pk, col1, col2, col3, col4
           FROM t
         UNION ALL
         SELECT 'new data' which, pk, col1, col2, col3, col4 FROM t_new)
 GROUP BY pk, col1, col2, col3, col4
HAVING COUNT(*) != 2
 ORDER BY pk, MIN(which);

Upvotes: 4

Doug Porter
Doug Porter

Reputation: 7887

This SO answer provides a very efficient solution using Oracle to compare the results of 2 queries: Proving SQL query equivalency

Upvotes: 4

LBushkin
LBushkin

Reputation: 131656

You want an exclusive-difference query, which is is essentially an (A-B) U (B-A) query:

(SELECT * FROM TODAY_TABLE
MINUS
SELECT * FROM YESTERDAY_TABLE)
UNION ALL
(SELECT * FROM YESTERDAY_TABLE
MINUS
SELECT * FROM TODAY_TABLE)

This query can also be easily improved to also show which records are inserts, deletes, and changes using an additional calculated column.

Upvotes: 3

Related Questions