0

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.

0

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 ...
                                )
                      */ 
0

Query 1:

SELECT *
    FROM current_table AS c
    LEFT JOIN previous_table AS p USING(some-combo-of-columns)
    WHERE (any-NOT-NULL-column-in-p) IS NULL;

It is quite naughty not to have a PRIMARY KEY on every table. Do you have a UNIQUE key? Such would work just as well (for this task).

Query 2:

SELECT c.*
    FROM current_table AS c
    LEFT JOIN previous_table AS p USING(some-combo-of-columns)
    WHERE (OR-of-!=-comparing-any-columns-that-might-be-different)

Please have more specifics in your question; then I can provide more specifics.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.