MySQL Comparison – How to Compare Tables and Generate Delta

MySQL

I have two exact tables with different data. I need to be able to generate a Mysql View with the following:

  1. All new rows that don't appear in previous_table but appears in current_table.
  2. All rows in previous_table that has updated column values in current_table.

The tables don't have any primary keys. I cannot change the schema because it is out of my control.

Any help would be deeply appreciated.

Best Answer

1)

SELECT t1.*
FROM current_table t1
LEFT JOIN previous_table t2 ON t1.field1 = t2.field1
                           AND t1.field2 = t2.field2
                     --    another fields in tables structures
                           AND t1.fieldN = t2.fieldN
WHERE t2.fieldX IS NULL /* fieldX - any field defined as NOT NULL */

2)

SELECT t2.*, 
       t1.fieldZ -- new value
FROM current_table t1
INNER JOIN previous_table t2 ON t1.field1 = t2.field1
                            AND t1.field2 = t2.field2
                      --    another static fields in tables structures
                            AND t1.fieldN = t2.fieldN
                      --    altered field
                            AND t1.fieldZ != t2.fieldZ
                      /* if more than one field may be changed, then
                            AND (     t1.fieldZa != t2.fieldZa
                                   OR t1.fieldZb != t2.fieldZb
                               --  OR ...
                                )
                      */
Related Question